
-- This scripts contains following view/synonym's definition:
-- =============================================================================
-- [DBA,ALL,USER]_CATALOG
-- [DBA,ALL,USER]_CONS_COLUMNS
-- [DBA,ALL,USER]_CONSTRAINTS
-- [DBA,ALL,USER]_DATA_FILES
-- [DBA,ALL,USER]_DEPENDENCIES
-- [DBA,ALL,USER]_IND_COLUMNS
-- [DBA,ALL,USER]_IND_EXPRESSIONS
-- [DBA,ALL,USER]_IND_PARTITIONS
-- [DBA,ALL,USER]_IND_STATISTICS
-- [DBA,ALL,USER]_INDEX_USAGE
-- [DBA,ALL,USER]_INDEXES
-- [DBA,ALL,USER]_OBJECTS
-- [DBA,ALL,USER]_PART_INDEXES
-- [DBA,ALL,USER]_PART_TABLES
-- [DBA,ALL,USER]_PROCEDURES
-- [DBA,ALL,USER]_SEGMENTS
-- [DBA,ALL,USER]_SEQUENCES
-- [DBA,ALL,USER]_SOURCE
-- [DBA,ALL,USER]_SYNONYMS
-- [DBA,ALL,USER]_TAB_COL_STATISTICS
-- [DBA,ALL,USER]_TAB_COLS
-- [DBA,ALL,USER]_TAB_COLUMNS
-- [DBA,ALL,USER]_TAB_COMMENTS
-- [DBA,ALL,USER]_COL_COMMENTS
-- [DBA,ALL,USER]_TAB_MODIFICATIONS
-- [DBA,ALL,USER]_TAB_PARTITIONS
-- [DBA,ALL,USER]_TAB_SUBPARTITIONS
-- [DBA,ALL,USER]_PART_KEY_COLUMNS
-- [DBA,ALL,USER]_SUBPART_KEY_COLUMNS
-- [DBA,ALL,USER]_TAB_STATISTICS
-- [DBA,ALL,USER]_TABLES
-- [DBA,ALL,USER]_TABLESPACES
-- [DBA,ALL,USER]_TRIGGER_COLS
-- [DBA,ALL,USER]_TRIGGERS
-- [DBA,ALL,USER]_TYPES
-- [DBA,ALL,USER]_VIEWS
-- [DBA,ALL,USER]_JOBS
-- [DBA,ALL,USER]_JOBS_RUNNING
-- [DBA,ALL,USER]_MVIEWS
-- [DBA,ALL,USER]_MVIEW_LOGS
-- [DBA,ALL,USER]_MVIEW_COMMENTS
-- [DBA,ALL,USER]_USERS
-- [DBA,ALL]_DIRECTORIES
-- [DBA,ALL,USER]_ARGUMENTS
-- DBA_ROLES
-- [DBA,USER]_ROLE_PRIVS
-- [DBA,ALL,USER]_TAB_PRIVS
-- PRODUCT_COMPONENT_VERSION
-- PLAN_TABLE
-- DBA_SOURCE_ALL
-- NLS_DATABASE_PARAMETERS
-- NLS_INSTANCE_PARAMETERS
-- NLS_SESSION_PARAMETERS

-- COLS
-- DICT
-- DICTIONARY
-- DUAL (For 2.0 and above)
-- IND
-- OBJ
-- TAB

-- [GV,V]$DATABASE
-- [GV,V]$INSTANCE
-- [GV,V]$DATAFILE
-- [GV,V]$LOCK
-- [GV,V]$PARAMETER
-- [GV,V]$PARAMETER_VALID_VALUES
-- [GV,V]$SESSION
-- [GV,V]$SESSTAT
-- [GV,V]$SPPARAMETER
-- [GV,V]$SYSSTAT
-- [GV,V]$TABLESPACE
-- [GV,V]$VERSION
-- [GV,V]$NLS_PARAMETERS
-- [GV,V]$NLS_VALID_VALUES
-- [GV,V]$SYSTEM_WAIT_CLASS
-- [GV,V]$SQL
-- [GV,V]$OPTION
-- [GV,V]$LICENSE
-- [GV,V]$STATNAME
-- [GV,V]$MYSTAT
-- [GV,V]$LISTENER_NETWORK

-- DBA_DETAIL_PRIVILEGES
-- DBA_ALL_PRIVILEGES
-- DBA_ALL_PRIVILEGES_SQL
-- =============================================================================


set client_min_messages='warning';
set statement_timeout = 60000;
set behavior_compat_options = '';


-- =============================================================================
-- Create Schema
-- =============================================================================
do $$
declare
    l_cnt       bigint;
    l_version   varchar(10);
begin
    select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'compat_tools';
    if l_cnt = 0
    then
        create schema compat_tools;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table
-- =============================================================================
create table if not exists compat_tools.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION, PROCEDURE
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);


-- =============================================================================
-- Table: compat_tools.pg_function_list
-- =============================================================================
drop table if exists compat_tools.pg_function_list;
create table compat_tools.pg_function_list as
select p.oid
     , l.lanname as language
     , n.nspname as schema_name
     , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as function_name
  from (select oid
             , pronamespace
             , proname
             , prolang
             , case when proallargtypes is null then proargtypes else proallargtypes end as proallargtypes
             , generate_series(1, array_length(case when proallargtypes is null then proargtypes else proallargtypes end, 1)) as id
             , unnest(case when proallargtypes is null then proargtypes else proallargtypes end) as protype
          from pg_catalog.pg_proc
       ) as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
  join pg_catalog.pg_type as t on p.protype = t.oid
  left join pg_catalog.pg_type as a on t.typcategory = 'A' and t.typelem = a.oid   -- for array type
 group by p.proname, p.proallargtypes, l.lanname, p.oid, n.nspname
 union all
select p.oid, l.lanname, n.nspname, p.proname||'()'
  from pg_catalog.pg_proc as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
 where (oidvectortypes(p.proargtypes) is null or oidvectortypes(p.proargtypes) = '');


-- =============================================================================
-- Table: compat_tools.pg_depend_list
-- =============================================================================
drop table if exists compat_tools.pg_depend_list;
create table compat_tools.pg_depend_list as
select distinct dep.classid::regclass::text as object_type
     , coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) as schema_name
     , coalesce(typ.typname, coalesce(cls.relname, dep.objid::text)) as object_name
     , dep.refclassid::regclass::text as ref_object_type
     , cpt.object_name as ref_object_name
     , cpt.compat_type
     , case dep.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else dep.deptype::text end as DEPENDENCY_TYPE
  from pg_depend as dep
  join (select v.compat_type, v.object_name, l.language, coalesce(l.oid, c.oid) as oid
          from compat_tools.compat_version as v
          left join compat_tools.pg_function_list as l on v.object_name = l.schema_name||'.'||l.function_name
          left join (select cls.oid, nsp.nspname||'.'||cls.relname object_name
                       from pg_catalog.pg_class as cls
                       join pg_catalog.pg_namespace as nsp on cls.relnamespace = nsp.oid
                    ) as c on v.object_name = c.object_name
         where v.compat_type in ('aggregate', 'procedure', 'function', 'view')) as cpt on dep.refobjid = cpt.oid
  left join pg_type as typ on dep.classid = 'pg_type'::regclass and dep.objid = typ.oid
  left join pg_namespace as typ_n on typ_n.oid = typ.typnamespace
  left join pg_rewrite as rwt on dep.classid = 'pg_rewrite'::regclass and dep.objid = rwt.oid
  left join pg_class as cls on rwt.ev_class = cls.oid
  left join pg_namespace as cls_n on cls_n.oid = cls.relnamespace
 where coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) != 'compat_tools';


-- =============================================================================
-- Version Function
-- =============================================================================
drop function if exists compat_tools.drop_compat_object(varchar, varchar, varchar, varchar);
create or replace function compat_tools.drop_compat_object( p_object_type    varchar(10)
                                                          , p_object_name    varchar(128)
                                                          , p_object_version varchar(10)
                                                          , p_object_schema  varchar(128)  default 'compat_tools')
returns boolean
as $$
declare
    l_version       varchar(10);
    l_depend_cnt    bigint;
    l_app_name      varchar(128)  := current_setting('application_name');
    l_result        boolean       := 'true';
    l_operation     varchar(128);
begin
    CREATE TEMPORARY TABLE if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    ) ON COMMIT PRESERVE ROWS;

    -- 字符串参数统一转小写
    p_object_name := lower(p_object_name);
    p_object_type := lower(p_object_type);
    p_object_schema := lower(p_object_schema);

    -- 获取当前目标库中的对象版本
    select max(object_version) into l_version
      from compat_tools.compat_version
     where object_name = p_object_schema||'.'||p_object_name
       and compat_type = p_object_type;

    -- 获取非 Compat Tools 依赖对象数量
    select count(*) into l_depend_cnt
      from compat_tools.pg_depend_list
     where ref_object_name = p_object_schema||'.'||p_object_name;

    if l_version >= p_object_version
    then
        l_operation := 'Skip';
        l_result    := 'false';

        if l_app_name != 'checkMe'
        then
            insert into compat_tools.compat_version
            values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
            ON DUPLICATE KEY UPDATE NOTHING;
        end if;
    else
        l_operation := case when l_version is null then 'Initial creation' else 'Upgrade' end;
        l_result    := 'true';

        if l_app_name != 'checkMe'
        then
            begin
                -- 若系统中存在非 compat_tools 对象依赖本对象，无法删除，可尝试直接创建
                if l_depend_cnt = 0
                then
                    if instr(p_object_name, '(') > 0
                    then
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||replace(p_object_name, '(', '"(')||' cascade';
                    else
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||p_object_name||'" cascade';
                    end if;

                    insert into compat_tools.compat_version
                    values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
                    ON DUPLICATE KEY UPDATE object_version = p_object_version;
                else
                    l_operation := l_operation||' - dependence';
                end if;
            exception
                when others then
                    l_result := 'false';
                    get stacked diagnostics l_operation = message_text;
                    l_operation = substr(l_operation, 1, 128);
            end;
        end if;
    end if;

    -- 插入本次临时结果表
    insert into temp_result (object_type, object_name, local_version, script_version, operation)
    values (p_object_type, p_object_schema||'.'||p_object_name, l_version, p_object_version, l_operation);

    -- 返回函数结果
    if l_app_name = 'checkMe'
    then
        return 'false';
    else
        return l_result;
    end if;
end;
$$ language plpgsql;



-- =============================================================================
-- Version Comparison Function
-- Result:
--     version_a > version_b  => 1
--     version_a = version_b  => 0
--     version_a < version_b  => -1
-- =============================================================================
create or replace function pg_catalog.f_version_compare ( version_a text
                                                        , version_b text)
returns int IMMUTABLE strict as $$
declare
    l_rec   record;
begin
    if version_a !~ '^\d+(\.\d+)*$' or version_b !~ '^\d+(\.\d+)*$'
    then
        return null;
    end if;
    for l_rec in select coalesce(t1.ver, -1) as ver_1, coalesce(t2.ver, -1) as ver_2
                   from (select rownum as id, t1::int as ver from regexp_split_to_table(version_a, '\.') as t1) as t1
                   full outer join (select rownum as id, t2::int as ver from regexp_split_to_table(version_b, '\.') as t2) as t2
                     on t1.id = t2.id
    loop
        if l_rec.ver_1 > l_rec.ver_2
        then
            return 1;
        elsif l_rec.ver_1 < l_rec.ver_2
        then
            return -1;
        end if;
    end loop;

    return 0;
end;
$$ language plpgsql;



-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $VIEW_CREATION$
declare
    l_cnt    bigint;
begin
    -- =========================================================================
    -- FUNCTION: F_UPPER_NAME(text,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('FUNCTION', 'F_UPPER_NAME(text,text)', '2.0')
        then
            create or replace function compat_tools.f_upper_name(name text,convert_ind text default 'Y')
            returns text immutable as
            $$ select case when convert_ind='Y' then upper(name) else name end;
            $$ language sql;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in F_UPPER_NAME(text,text): %', SQLERRM;
            rollback;
    end;

    -- =========================================================================
    -- FUNCTION: MG_SEQUENCE()
    -- 20231124 2.2版本，指定owner字段的数据类型，确保输出类型稳定
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('FUNCTION', 'MG_SEQUENCE()', '2.2')
        then
        if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.9.9') = 1 then
            create or replace function compat_tools.mg_sequence()
            returns setof record
            language plpgsql
            as $$
            declare
                l_seq_item   record;
            begin
                for l_seq_item in SELECT nc.nspname as owner,
                                         c.relname AS sequence_name
                                    FROM pg_catalog.pg_namespace nc
                                    join pg_catalog.pg_class c on c.relnamespace = nc.oid
                                  WHERE c.relkind in ( 'S'::"char",'L'::"char")
                                    AND NOT pg_is_other_temp_schema(nc.oid)
                                    AND has_sequence_privilege((case when c.relkind in ('S'::"char",'L'::"char") then c.oid end),'SELECT'::text)
                loop
                    return query execute 'select tableoid, '''||l_seq_item.owner||'''::text as owner, sequence_name, last_value::int16, start_value::int16, increment_by::int16, max_value::int16, min_value::int16, cache_value::int16, is_cycled from '||l_seq_item.owner||'.'||l_seq_item.sequence_name;
                end loop;
            end;
            $$;
        else
            create or replace function compat_tools.mg_sequence()
            returns setof record
            language plpgsql
            as $$
            declare
                l_seq_item   record;
            begin
                for l_seq_item in SELECT nc.nspname as owner,
                                         c.relname AS sequence_name
                                    FROM pg_catalog.pg_namespace nc
                                    join pg_catalog.pg_class c on c.relnamespace = nc.oid
                                  WHERE c.relkind in ( 'S'::"char",'L'::"char")
                                    AND NOT pg_is_other_temp_schema(nc.oid)
                                    AND has_sequence_privilege((case when c.relkind='S'::"char" then c.oid end),'SELECT'::text)
                loop
                    return query execute 'select tableoid, '''||l_seq_item.owner||''' as owner, sequence_name, last_value, start_value, increment_by, max_value, min_value, cache_value, is_cycled from '||l_seq_item.owner||'.'||l_seq_item.sequence_name;
                end loop;
            end;
            $$;
        end if;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in MG_SEQUENCE(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_SEQUENCES
    -- ALL_SEQUENCES
    -- USER_SEQUENCES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_SEQUENCES', '4.1')
        then
        if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.9.9') = 1 then
            CREATE OR REPLACE VIEW compat_tools.DBA_SEQUENCES
            AS
            select sequence_oid as sequence_oid
                 , case when owner::text = lower(owner::text) then compat_tools.f_upper_name(owner::text) else owner::text end AS SEQUENCE_OWNER
                 , case when sequence_name::text = lower(sequence_name::text) then compat_tools.f_upper_name(sequence_name::text) else sequence_name::text end AS SEQUENCE_NAME
                 , min_value
                 , max_value
                 , increment_by
                 , case when is_cycled then 'Y' else 'N' end AS CYCLE_FLAG
                 , cache_value as CACHE_SIZE
                 , last_value AS LAST_NUMBER
                 , start_value
              from compat_tools.mg_sequence()
                as ( sequence_oid oid
                   , owner text
                   , sequence_name name
                   , last_value int16
                   , start_value int16
                   , increment_by int16
                   , max_value int16
                   , min_value int16
                   , cache_value int16
                   , is_cycled boolean);
        else
            CREATE OR REPLACE VIEW compat_tools.DBA_SEQUENCES
            AS
            select sequence_oid as sequence_oid
                 , case when owner::text = lower(owner::text) then compat_tools.f_upper_name(owner::text) else owner::text end AS SEQUENCE_OWNER
                 , case when sequence_name::text = lower(sequence_name::text) then compat_tools.f_upper_name(sequence_name::text) else sequence_name::text end AS SEQUENCE_NAME
                 , min_value
                 , max_value
                 , increment_by
                 , case when is_cycled then 'Y' else 'N' end AS CYCLE_FLAG
                 , cache_value as CACHE_SIZE
                 , last_value AS LAST_NUMBER
                 , start_value
              from compat_tools.mg_sequence()
                as ( sequence_oid oid
                   , owner text
                   , sequence_name name
                   , last_value bigint
                   , start_value bigint
                   , increment_by bigint
                   , max_value bigint
                   , min_value bigint
                   , cache_value bigint
                   , is_cycled boolean);
        end if;
            CREATE OR REPLACE VIEW compat_tools.USER_SEQUENCES AS SELECT sequence_oid, SEQUENCE_NAME , min_value, max_value, increment_by, CYCLE_FLAG, CACHE_SIZE, LAST_NUMBER, start_value
              FROM compat_tools.DBA_SEQUENCES WHERE SEQUENCE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_SEQUENCES for compat_tools.DBA_SEQUENCES;
            CREATE OR REPLACE SYNONYM public.ALL_SEQUENCES for compat_tools.DBA_SEQUENCES;
            CREATE OR REPLACE SYNONYM public.USER_SEQUENCES for compat_tools.USER_SEQUENCES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_SEQUENCES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_SYNONYMS
    -- ALL_SYNONYMS
    -- USER_SYNONYMS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_SYNONYMS', '2.0')
        then
            create or replace view compat_tools.DBA_SYNONYMS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when s.synname::text = lower(s.synname::text) then compat_tools.f_upper_name(s.synname::text) else s.synname::text end AS SYNONYM_NAME
                 , case when s.synobjschema::text = lower(s.synobjschema::text) then compat_tools.f_upper_name(s.synobjschema::text) else s.synobjschema::text end AS TABLE_OWNER
                 , case when s.synobjname::text = lower(s.synobjname::text) then compat_tools.f_upper_name(s.synobjname::text) else s.synobjname::text end AS TABLE_NAME
              from pg_catalog.pg_synonym as s
              join pg_catalog.pg_namespace as n on s.synnamespace = n.oid;

            CREATE OR REPLACE VIEW compat_tools.USER_SYNONYMS AS SELECT SYNONYM_NAME, TABLE_OWNER , TABLE_NAME FROM compat_tools.DBA_SYNONYMS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_SYNONYMS for compat_tools.DBA_SYNONYMS;
            CREATE OR REPLACE SYNONYM public.ALL_SYNONYMS for compat_tools.DBA_SYNONYMS;
            CREATE OR REPLACE SYNONYM public.USER_SYNONYMS for compat_tools.USER_SYNONYMS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_SYNONYMS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TAB_COLS
    -- ALL_TAB_COLS
    -- USER_TAB_COLS
    -- COLS
    -- DBA_TAB_COLUMNS
    -- ALL_TAB_COLUMNS
    -- USER_TAB_COLUMNS
    -- =========================================================================
    -- 变更历史：
    --    2023-09-11 修复A模式下varchar类型data_length为null的问题
    --    2023-12-09 重写获取data_length的逻辑
    --    2025-02-17 新增DBA_TAB_COLUMNS_O_STYLE视图，适用于在特殊场景下使用O风格的数据类型名称；
    --               调整timestamp/timestamptz的数据类型名称，增加精度显示
    --    2025-03-10 增加char_length字段,用于显示字符长度
    --    2025-03-12 增加默认值转换
    --    2025-03-21 适配MogDB 5.0.12版本新增的列原始定义功能
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_COLS', '3.4')
        then
            create or replace function compat_tools.f_get_type_max_length(pg_attribute,pg_type)returns int4 immutable as
            $$
            select (case
            --bpchar(n) varchar(n) nvarchar(n)
            when ($1.atttypid in (1042,1043,3969) or $2.typbasetype in (1042,1043,3969)) and $1.atttypmod>0 then $1.atttypmod -4
            --bpchar varchar nvarchar
            when ($1.atttypid in (1042,1043,3969) or $2.typbasetype in (1042,1043,3969)) and $1.atttypmod<0 then 10*2^10 -- 10M
            --numeric
            when $1.atttypid in (1700) or $2.typbasetype in (1700) then 2^16 -- actually,extra + 9 bytes ,64K
            --text clob json xml unknown hstore
            when $1.atttypid in (25,90,114,142,705,15601) or $2.typbasetype in (25,90,114,142,705,15601) then 2^30-1 -- 1G-1
            --bytea raw blob jsonb
            when $1.atttypid in (17,86,88,3802) or $2.typbasetype in (17,86,88,3802) then 2^30-8203 -- 1G-8203
            --byteawithoutorderwithequalcol byteawithoutordercol
            when $1.atttypid in (4402,4403) or $2.typbasetype in (4402,4403) then 2^30-53 -- 1G-53
            --bit varbit
            when $1.atttypid in (1560,1562) or $2.typbasetype in (1560,1562) then $1.atttypmod
            --inet cidr
            when $1.atttypid in (869,650) or $2.typbasetype in (869,650) then 2^4+3 -- 16+3
            --hll
            when $1.atttypid in (4301) or $2.TYPBASETYPE in (4301) then 2^14 -- 16K
            --dolphin bin type
            when $2.typname in ('binary','tinyblob') then 2^8-1 -- 255B
            when $2.typname in ('mediumblob') then 2^14-1 -- 16K-1
            when $2.typname in ('varbinary') then 2^16-1  -- 64K-1
            when $2.typname in ('longblob') then 2^32-1   -- 4G-1
            --others
            else $1.attlen end)::int4;
            $$language sql;

            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COLS
            AS
            SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
                 , case when cls.relname::text = lower(cls.relname::text) then compat_tools.f_upper_name(cls.relname::text) else cls.relname::text end AS TABLE_NAME
                 , case when att.attname::text = lower(att.attname::text) then compat_tools.f_upper_name(att.attname::text) else att.attname::text end AS COLUMN_NAME
                 , case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end||
                    case when typ.typname in ('timestamp','timestamptz') then ('('||CASE atttypmod WHEN -1 THEN 6 ELSE atttypmod END ||')')::TEXT END AS DATA_TYPE
                 , case when tnsp.nspname::text = lower(tnsp.nspname::text) then compat_tools.f_upper_name(tnsp.nspname::text) else tnsp.nspname::text end AS DATA_TYPE_OWNER
                 , compat_tools.f_get_type_max_length(att.*,typ.*) as DATA_LENGTH
                 , information_schema._pg_char_octet_length(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_LENGTH_OCTET
                 , information_schema._pg_numeric_precision(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_PRECISION
                 , case when typ.typname in ('timestamp','timestamptz') then (CASE atttypmod WHEN -1 THEN 6 ELSE atttypmod END ) 
                    else information_schema._pg_numeric_scale(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) end as DATA_SCALE
                 , CASE when att.attnotnull THEN 'N'::text ELSE 'Y'::text END AS NULLABLE
                 , att.attnum as COLUMN_ID
                 , length(pg_get_expr(ad.adbin, ad.adrelid)) as DEFAULT_LENGTH
                 , pg_get_expr(ad.adbin, ad.adrelid) as DATA_DEFAULT
                 , CASE WHEN stat.n_distinct >= 0 THEN stat.n_distinct ELSE ROUND(ABS(stat.n_distinct * cls.RELTUPLES)) END as NUM_DISTINCT
                 , stat.correlation
                 , stat.NULL_FRAC * cls.RELTUPLES AS NUM_NULLS
                 , stat.avg_width as AVG_COL_LEN
                 , case TYP.TYPCATEGORY when 'S' then compat_tools.f_get_type_max_length(att.*,typ.*) else 0 end as char_length
              FROM pg_catalog.pg_attribute att
              JOIN pg_catalog.pg_type typ on att.atttypid = typ.oid
              JOIN pg_catalog.pg_namespace tnsp on typ.typnamespace = tnsp.oid
              JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid
              JOIN pg_catalog.pg_namespace nsp on cls.relnamespace = nsp.oid
              LEFT JOIN pg_catalog.PG_STATS stat ON nsp.nspname = stat.schemaname AND cls.relname = stat.tablename AND att.attname = stat.attname
              LEFT JOIN pg_catalog.pg_attrdef ad ON att.attrelid = ad.adrelid AND att.attnum = ad.adnum
             WHERE nsp.nspname NOT LIKE 'pg_toast%'
               AND cls.relkind in ('r', 'v', 't', 'f');    -- 限制表和视图

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_COLS
            AS SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_OWNER, DATA_LENGTH, DATA_LENGTH_OCTET, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, CORRELATION, NUM_NULLS, AVG_COL_LEN,char_length
                 FROM compat_tools.DBA_TAB_COLS WHERE OWNER  = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COLUMNS AS SELECT * FROM compat_tools.DBA_TAB_COLS WHERE COLUMN_ID >= 1;
            
            select count(1) into l_cnt FROM pg_class pc ,pg_namespace pn WHERE pc.relnamespace=pn.oid AND  relname='pg_attr_origin_define' AND nspname='pg_catalog';
           
            if l_cnt > 0 then
            create or replace view compat_tools.DBA_TAB_COLUMNS_O_STYLE as 
            select 
             owner,table_name,column_name,data_type,data_type_owner,data_length,data_length_octet,data_precision,data_scale,
             row_number() over(partition by owner,table_name order by column_id) as column_id,
             nullable,default_length,data_default,num_distinct,correlation,num_nulls,avg_col_len,char_length 
            from 
            ( SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
                 , case when cls.relname::text = lower(cls.relname::text) then compat_tools.f_upper_name(cls.relname::text) else cls.relname::text end AS TABLE_NAME
                 , case when att.attname::text = lower(att.attname::text) then compat_tools.f_upper_name(att.attname::text) else att.attname::text end AS COLUMN_NAME
                 , case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(nvl(regexp_replace(od.aodtype,'(?i)(.+)\(.+\)','\1'),typ.typname)::text) else typ.typname::text end||
                    case when nvl(regexp_replace(od.aodtype,'(?i)(.+)\(.+\)','\1'),typ.typname)::text in ('timestamp','timestamptz') then ('('||CASE atttypmod WHEN -1 THEN 6 ELSE atttypmod END ||')')::TEXT END AS DATA_TYPE
                 , case when tnsp.nspname::text = lower(tnsp.nspname::text) then compat_tools.f_upper_name(tnsp.nspname::text) else tnsp.nspname::text end AS DATA_TYPE_OWNER
                 , compat_tools.f_get_type_max_length(att.*,typ.*) as DATA_LENGTH
                 , information_schema._pg_char_octet_length(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_LENGTH_OCTET
                 , information_schema._pg_numeric_precision(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_PRECISION
                 , case when typ.typname in ('timestamp','timestamptz') then (CASE atttypmod WHEN -1 THEN 6 ELSE atttypmod END ) 
                    else information_schema._pg_numeric_scale(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) end as DATA_SCALE
                 , CASE when att.attnotnull THEN 'N'::text ELSE 'Y'::text END AS NULLABLE
                 , attnum COLUMN_ID
                 , length(pg_get_expr(ad.adbin, ad.adrelid)) as DEFAULT_LENGTH
                 , nvl(aoddefault,pg_get_expr(ad.adbin, ad.adrelid)) as DATA_DEFAULT
                 , CASE WHEN stat.n_distinct >= 0 THEN stat.n_distinct ELSE ROUND(ABS(stat.n_distinct * cls.RELTUPLES)) END as NUM_DISTINCT
                 , stat.correlation
                 , stat.NULL_FRAC * cls.RELTUPLES AS NUM_NULLS
                 , stat.avg_width as AVG_COL_LEN
                 , case TYP.TYPCATEGORY when 'S' then compat_tools.f_get_type_max_length(att.*,typ.*) else 0 end as char_length
              FROM pg_catalog.pg_attribute att
              JOIN pg_catalog.pg_type typ on att.atttypid = typ.oid
              JOIN pg_catalog.pg_namespace tnsp on typ.typnamespace = tnsp.oid
              JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid
              JOIN pg_catalog.pg_namespace nsp on cls.relnamespace = nsp.oid
              LEFT JOIN pg_catalog.PG_STATS stat ON nsp.nspname = stat.schemaname AND cls.relname = stat.tablename AND att.attname = stat.attname
              LEFT JOIN pg_catalog.pg_attrdef ad ON att.attrelid = ad.adrelid AND att.attnum = ad.adnum
              left join pg_catalog.pg_attr_origin_define od on att.attrelid=od.aodrelid and att.attnum=od.aodnum
             WHERE nsp.nspname NOT LIKE 'pg_toast%'
               AND cls.relkind in ('r', 'v', 't', 'f')
               and attnum>=1 ) ;

            create or replace view compat_tools.USER_TAB_COLUMNS_O_STYLE as 
            select 
             owner,table_name,column_name,data_type,data_type_owner,data_length,data_length_octet,data_precision,data_scale,
             row_number() over(partition by owner,table_name order by column_id) as column_id,
             nullable,default_length,data_default,num_distinct,correlation,num_nulls,avg_col_len,char_length 
            from 
            ( SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
                 , case when cls.relname::text = lower(cls.relname::text) then compat_tools.f_upper_name(cls.relname::text) else cls.relname::text end AS TABLE_NAME
                 , case when att.attname::text = lower(att.attname::text) then compat_tools.f_upper_name(att.attname::text) else att.attname::text end AS COLUMN_NAME
                 , case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(nvl(regexp_replace(od.aodtype,'(?i)(.+)\(.+\)','\1'),typ.typname)::text) else typ.typname::text end||
                    case when nvl(regexp_replace(od.aodtype,'(?i)(.+)\(.+\)','\1'),typ.typname)::text in ('timestamp','timestamptz') then ('('||CASE atttypmod WHEN -1 THEN 6 ELSE atttypmod END ||')')::TEXT END AS DATA_TYPE
                 , case when tnsp.nspname::text = lower(tnsp.nspname::text) then compat_tools.f_upper_name(tnsp.nspname::text) else tnsp.nspname::text end AS DATA_TYPE_OWNER
                 , compat_tools.f_get_type_max_length(att.*,typ.*) as DATA_LENGTH
                 , information_schema._pg_char_octet_length(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_LENGTH_OCTET
                 , information_schema._pg_numeric_precision(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_PRECISION
                 , case when typ.typname in ('timestamp','timestamptz') then (CASE atttypmod WHEN -1 THEN 6 ELSE atttypmod END ) 
                    else information_schema._pg_numeric_scale(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) end as DATA_SCALE
                 , CASE when att.attnotnull THEN 'N'::text ELSE 'Y'::text END AS NULLABLE
                 , attnum COLUMN_ID
                 , length(pg_get_expr(ad.adbin, ad.adrelid)) as DEFAULT_LENGTH
                 , nvl(aoddefault,pg_get_expr(ad.adbin, ad.adrelid)) as DATA_DEFAULT
                 , CASE WHEN stat.n_distinct >= 0 THEN stat.n_distinct ELSE ROUND(ABS(stat.n_distinct * cls.RELTUPLES)) END as NUM_DISTINCT
                 , stat.correlation
                 , stat.NULL_FRAC * cls.RELTUPLES AS NUM_NULLS
                 , stat.avg_width as AVG_COL_LEN
                 , case TYP.TYPCATEGORY when 'S' then compat_tools.f_get_type_max_length(att.*,typ.*) else 0 end as char_length
              FROM pg_catalog.pg_attribute att
              JOIN pg_catalog.pg_type typ on att.atttypid = typ.oid
              JOIN pg_catalog.pg_namespace tnsp on typ.typnamespace = tnsp.oid
              JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid
              JOIN pg_catalog.pg_namespace nsp on cls.relnamespace = nsp.oid
              LEFT JOIN pg_catalog.PG_STATS stat ON nsp.nspname = stat.schemaname AND cls.relname = stat.tablename AND att.attname = stat.attname
              LEFT JOIN pg_catalog.pg_attrdef ad ON att.attrelid = ad.adrelid AND att.attnum = ad.adnum
              left join pg_catalog.pg_attr_origin_define od on att.attrelid=od.aodrelid and att.attnum=od.aodnum
             WHERE nsp.nspname = current_schema()
               AND cls.relkind in ('r', 'v', 't', 'f')
               and attnum>=1 ) ;
            else 
            create or replace function compat_tools.default_value_o_style(text) returns text immutable strict as
            $$select regexp_replace(
            regexp_replace(
            regexp_replace(
            regexp_replace(
            regexp_replace(
            regexp_replace(
            regexp_replace(
            regexp_replace(
            regexp_replace(
            regexp_replace(
             $1,'"sysdate"\(\)','sysdate','g')
            ,'pg_systimestamp\(\)','current_timestamp','g')
            ,'text_date\(''now''::text\)','current_date','g')
            ,'\(''now''::text\)::timestamp\((\d)\) with time zone','current_timestamp(\1)','g')
            ,'(NULL)::(character varying|timestamp without time zone|timestamp\(\d\) without time zone|bpchar|clob|text|nvarchar2|"char"|name|bytea|raw|"bit"|bit varying)','\1','g')
            ,'(\))::(character varying|timestamp without time zone|timestamp\(\d\) without time zone|bpchar|clob|text|nvarchar2|"char"|name)','\1','g')
            ,'(''[^'']+'')::(text|character varying|bpchar|clob|nvarchar2|"char"|name)','\1','g')
            ,'(''[^'']+)\s00:00:00''::(timestamp\(0\) without time zone)','date\1''','g')
            ,'(''[^'']+'')::(timestamp without time zone)','timestamp\1','g')
            ,'\(\((''[^'']+'')\)\)','(\1)','g')
            $$language sql;

            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COLUMNS_O_STYLE
             AS /*not convert : int1、int2、int4、int8、float4*/
            select
                owner,
                table_name,
                column_name,
                case 
                    when data_type='NUMERIC' then 'NUMBER'
                    when data_type='numeric' then 'number'
                    when data_type='VARCHAR' then 'VARCHAR2' 
                    when data_type='varchar' then 'varchar2'
                    when data_type='BPCHAR' then 'CHAR'
                    when data_type='bpchar' then 'char'
                    when data_type='TIMESTAMP(0)' then 'DATE'
                    when data_type='timestamp(0)' then 'date'
                    when regexp_like(data_type,'(?i)(float)8') then regexp_replace(data_type,'(?i)(float)8','\1')
                    when regexp_like(data_type,'timestamptz\(\d\)') then regexp_replace(data_type,'timestamptz\((\d)\)','timestamp(\1) with time zone')
                    when regexp_like(data_type,'TIMESTAMPTZ\(\d\)') then regexp_replace(data_type,'TIMESTAMPTZ\((\d)\)','TIMESTAMP(\1) WITH TIME ZONE')
                    else data_type 
                end as data_type,
                data_type_owner,
                data_length,
                data_length_octet,
                data_precision,
                case when data_type in ('timestamp(0)','TIMESTAMP(0)') then null else  data_scale end as data_scale,
                nullable,
                row_number() over(partition by owner,table_name order by column_id) as column_id,
                default_length,
                compat_tools.default_value_o_style(data_default) as data_default,
                num_distinct,
                correlation,
                num_nulls,
                avg_col_len,
                char_length
            from compat_tools.DBA_TAB_COLS WHERE COLUMN_ID >= 1;

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_COLUMNS_O_STYLE
             AS /*not convert : int1、int2、int4、int8、float4*/
            select
                owner,
                table_name,
                column_name,
                case 
                    when data_type='NUMERIC' then 'NUMBER'
                    when data_type='numeric' then 'number'
                    when data_type='VARCHAR' then 'VARCHAR2' 
                    when data_type='varchar' then 'varchar2'
                    when data_type='BPCHAR' then 'CHAR'
                    when data_type='bpchar' then 'char'
                    when data_type='TIMESTAMP(0)' then 'DATE'
                    when data_type='timestamp(0)' then 'date'
                    when regexp_like(data_type,'(?i)(float)8') then regexp_replace(data_type,'(?i)(float)8','\1')
                    when regexp_like(data_type,'timestamptz\(\d\)') then regexp_replace(data_type,'timestamptz\((\d)\)','timestamp(\1) with time zone')
                    when regexp_like(data_type,'TIMESTAMPTZ\(\d\)') then regexp_replace(data_type,'TIMESTAMPTZ\((\d)\)','TIMESTAMP(\1) WITH TIME ZONE')
                    else data_type 
                end as data_type,
                data_type_owner,
                data_length,
                data_length_octet,
                data_precision,
                case when data_type in ('timestamp(0)','TIMESTAMP(0)') then null else  data_scale end as data_scale,
                nullable,
                row_number() over(partition by owner,table_name order by column_id) as column_id,
                default_length,
                compat_tools.default_value_o_style(data_default) as data_default,
                num_distinct,
                correlation,
                num_nulls,
                avg_col_len,
                char_length
            from compat_tools.DBA_TAB_COLS 
              WHERE COLUMN_ID >= 1 
              AND OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
            end if;

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_COLUMNS AS SELECT * FROM compat_tools.USER_TAB_COLS WHERE COLUMN_ID >= 1;

            CREATE OR REPLACE SYNONYM public.DBA_TAB_COLS for compat_tools.DBA_TAB_COLS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_COLS for compat_tools.DBA_TAB_COLS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_COLS for compat_tools.USER_TAB_COLS;
            CREATE OR REPLACE SYNONYM public.COLS for compat_tools.DBA_TAB_COLS;
            CREATE OR REPLACE SYNONYM public.DBA_TAB_COLUMNS for compat_tools.DBA_TAB_COLUMNS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_COLUMNS for compat_tools.DBA_TAB_COLUMNS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_COLUMNS for compat_tools.USER_TAB_COLUMNS;

            CREATE OR REPLACE SYNONYM public.DBA_TAB_COLUMNS_O_STYLE for compat_tools.DBA_TAB_COLUMNS_O_STYLE;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_COLUMNS_O_STYLE for compat_tools.DBA_TAB_COLUMNS_O_STYLE;
            CREATE OR REPLACE SYNONYM public.USER_TAB_COLUMNS_O_STYLE for compat_tools.USER_TAB_COLUMNS_O_STYLE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_COLS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TAB_COL_STATISTICS
    -- ALL_TAB_COL_STATISTICS
    -- USER_TAB_COL_STATISTICS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_COL_STATISTICS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COL_STATISTICS
            AS
            SELECT OWNER
                 , TABLE_NAME
                 , COLUMN_NAME
                 , NUM_DISTINCT
                 , CORRELATION
                 , NUM_NULLS
                 , AVG_COL_LEN
              FROM compat_tools.DBA_TAB_COLUMNS;

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_COL_STATISTICS AS SELECT TABLE_NAME , COLUMN_NAME, NUM_DISTINCT, CORRELATION, NUM_NULLS, AVG_COL_LEN FROM compat_tools.DBA_TAB_COL_STATISTICS WHERE OWNER  = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TAB_COL_STATISTICS for compat_tools.DBA_TAB_COL_STATISTICS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_COL_STATISTICS for compat_tools.DBA_TAB_COL_STATISTICS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_COL_STATISTICS for compat_tools.USER_TAB_COL_STATISTICS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_COL_STATISTICS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_OBJECTS
    -- ALL_OBJECTS
    -- USER_OBJECTS
    -- OBJ
    -- =========================================================================
    -- 2022-01-25  [1.1 -> 2.0]    添加 TABLE/INDEX SUBPARTITION 对象类型的识别
    -- 2022-04-20  [2.0 -> 3.0]    针对 openGauss 2.1 版本，增加 Package 相关对象
    -- 2023-12-16  [4.0 -> 5.0]    针对有plsql编译依赖功能及type-object功能的数据库版本重写dba_objects视图
    -- 2025-02-07  [5.1 -> 5.2]    针对无PLSQL编译依赖及type-object功能的数据库版本重写dba_objects视图
    -- 2025-02-25  [5.2 -> 5.3]    增加匿名块语法触发器查询
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_OBJECTS', '5.3')
        then
        select count(1) into l_cnt from pg_attribute  
         where (attrelid=(SELECT pc.oid FROM pg_class pc ,pg_namespace pn WHERE pc.relnamespace=pn.oid AND  relname='gs_package' AND nspname='pg_catalog') and attname='isobject') 
            or (attrelid='pg_object'::regclass and attname='valid');
        if l_cnt=2 then 
            CREATE OR REPLACE VIEW compat_tools.DBA_OBJECTS AS
            select 
            case when "owner"::regnamespace::text = lower("owner"::regnamespace::text) then compat_tools.f_upper_name("owner"::regnamespace::text) else "owner"::regnamespace::text end AS owner
                                , case when object_name::text = lower(object_name::text) then compat_tools.f_upper_name(object_name::text) else object_name::text end AS object_name
                                , case when subobject_name::text = lower(subobject_name::text) then compat_tools.f_upper_name(subobject_name::text) else subobject_name::text end AS SUBOBJECT_NAME
                                ,               
            object_id,data_object_id,object_type,created,last_ddl_time,"timestamp",
            status,"temporary","generated",secondary,"namespace",edition_name,
            compat_tools.f_upper_name(creator::regrole::text) as creator 
            from (
                --pg_class 1. 表、视图、物化视图、索引、序列
                select c.relnamespace as owner,
                c.relname as object_name,
                null ::text as subobject_name,
                c.oid as object_id,
                c.relfilenode as data_object_id,
                CASE relkind
                    WHEN 'r' ::"char" THEN 'TABLE' ::text
                    WHEN 'v' ::"char" THEN 'VIEW' ::text
                    WHEN 'm' ::"char" THEN 'MATERIALIZED VIEW' ::text
                    WHEN 'i' ::"char" THEN 'INDEX' ::text
                    WHEN 'I' ::"char" THEN 'GLOBAL INDEX' ::text
                    WHEN 'S' ::"char" THEN 'SEQUENCE' ::text
                    WHEN 'f' ::"char" THEN 'FOREIGN TABLE' ::text
                    WHEN 'c' ::"char" THEN 'COMPOSITE TYPE' ::text
                    WHEN 't' ::"char" THEN 'TOAST' ::text
                    when 'L' ::"char" then 'LARGE SEQUENCE' ::text
                    ELSE  relkind ::text
                END AS object_type,
                obj.ctime::timestamp(0) without time zone  as created,
                obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
                case when c.relkind in ('v','m') then (case rr.ev_enabled when 'D' then 'INVALID' else 'VALID' END) else 'VALID' end  as STATUS,
                case  when relpersistence in ('t', 'g') THEN  'Y'  else  'N'  end as temporary,
                null ::text as generated,
                null ::text as SECONDARY,
                1 ::int1 as namespace,
                null ::text as EDITION_NAME,
                relowner ::regrole as creator
            from pg_class c
            left join pg_object obj
                on obj.object_oid = c.oid
            left join pg_rewrite rr 
                on c.oid=rr.ev_class 
            union all
            --pg_partition  2. 分区/子分区对象
            SELECT CASE p.parttype
                    WHEN 's' THEN  st.relnamespace  ELSE  pp.relnamespace END AS owner,
                CASE p.parttype  WHEN 's' THEN  st.relname  ELSE  pp.relname END AS object_name,
                p.relname as SUBOBJECT_NAME,
                p.oid AS OBJECT_ID,
                p.relfilenode as DATA_OBJECT_ID,
                CASE p.parttype  
                    WHEN 'p' ::"char" THEN  'TABLE PARTITION' ::text
                    WHEN 'x' ::"char" THEN  (CASE it.parttype  WHEN 'p' THEN  'INDEX PARTITION'
                        ELSE  'INDEX SUBPARTITION'  end) ::text
                    WHEN 't' ::"char" THEN 'TOAST PARTITION' ::text
                    WHEN 's' ::"char" THEN  'TABLE SUBPARTITION' ::text
                    ELSE  p.parttype ::text
                END AS object_type,
                null ::timestamp(0) without time zone  as created,
                null ::timestamp(0) without time zone  as last_ddl_time,
                null ::text as timestamp,
                'VALID' as status,
                'N' as temporary,
                null ::text as generated,
                null ::text as SECONDARY,
                1 ::int1 as namespace,
                null ::text as EDITION_NAME,
                CASE p.parttype
                    WHEN 's' THEN  st.relowner  ELSE  pp.relowner
                END as creator 
            FROM pg_catalog.pg_partition as p -- 分区/子分区
            left join pg_catalog.pg_class as pp
                on p.parentid = pp.oid -- 分区的 parent
            left join pg_catalog.pg_partition as sp
                on p.parentid = sp.oid -- 子分区的分区
            left join pg_catalog.pg_class as st
                on sp.parentid = st.oid -- 子分区的分区的 parent
            left join pg_catalog.pg_partition as it
                on p.indextblid = it.oid -- 分区索引的 table (分区/子分区)
            where p.parttype in ('p', 'x', 't', 's')
            union all
            --pg_proc 3.函数、过程、触发器
            SELECT pronamespace AS owner,
                proname AS object_name,
                null as sub_object_name,
                p.OID as OBJECT_ID,
                null as DATA_OBJECT_ID,
                CASE
                    WHEN prorettype = 'trigger' ::regtype ::oid THEN  'TRIGGER' ::text
                    ELSE
                    (case prokind  when 'p' then  'PROCEDURE'  else  'FUNCTION'  end)
                END AS object_type,
                obj.ctime::timestamp(0) without time zone  as created,
                obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
                case
                    when obj."valid" or obj."valid" is null then  'VALID'  else  'INVALID'
                end as status,
                'N' as temporary,
                null ::text as generated,
                null ::text as SECONDARY,
                1 ::int1 as namespace,
                null ::text as EDITION_NAME,
                proowner as creator
            FROM pg_catalog.pg_proc p
            left join pg_object obj
                on obj.object_oid = p.oid
            WHERE propackageid = 0
            and ((p.prorettype <> 'trigger' ::regtype ::oid) 
                      or (p.prorettype = 'trigger' ::regtype ::oid 
                    and not exists (select 1 from pg_catalog.pg_trigger tri 
                                           where tri.tgfoid=p.oid 
                                           and nvl(jsonb_extract_path (row_to_json(tri)::jsonb,'tgfbody'),'null') <>'null'::jsonb))
                    )
            union all 
                --pg_proc 匿名块语法触发器
                select relnamespace AS owner,
                    tri.tgname AS object_name,
                    null as sub_object_name,
                    tri.OID as OBJECT_ID,
                    null as DATA_OBJECT_ID,
                    'TRIGGER'::text AS object_type,
                    obj.ctime::timestamp(0) without time zone  as created,
                    obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                    to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
                    /*case
                        when obj."valid" or obj."valid" is null then  'VALID'  else  'INVALID'
                    end*/'VALID' as status,
                    'N' as temporary,
                    null ::text as generated,
                    null ::text as SECONDARY,
                    3 ::int1 as namespace,
                    null ::text as EDITION_NAME,
                    tri.tgowner as creator
                from pg_catalog.pg_trigger tri
                left join pg_catalog.pg_object obj
                    on obj.object_oid=tri.oid
                left join pg_class cls
                    on tri.tgrelid=cls.oid
                where nvl(jsonb_extract_path (row_to_json(tri)::jsonb,'tgfbody'),'null') <>'null'::jsonb
            union all
            --pg_synonym 4.同义词
            SELECT synnamespace as owner,
                synname as object_name,
                null as sub_object_name,
                oid as object_Id,
                null as DATA_OBJECT_ID,
                'SYNONYM' as object_type,
                obj.ctime::timestamp(0) without time zone  as created,
                    obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                    to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
                'VALID' as status,
                'N' as temporary,
                null ::text as generated,
                null ::text as SECONDARY,
                1 ::int1 as namespace,
                null ::text as EDITION_NAME,
                synowner as creator
            from pg_synonym syn
            left join pg_object obj
                on syn.oid = obj.object_oid and obj.object_type = 'n'
            union all
            --gs_package 5.plsql包、plsql类型
            select pkg.pkgnamespace,
                pkg.pkgname,
                null as sub_object_name,
                case
                    when pkg.isobject then  tp.oid  else  pkg.oid
                end as object_Id,
                null as DATA_OBJECT_ID,
                case
                    when pkg.isobject then  'TYPE'
                    else  'PACKAGE'
                end || 
                case OBJ.object_type  when 'B' then ' BODY'
                end as object_type,
                obj.ctime::timestamp(0) without time zone  as created,
                obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as timestamp,
                case
                    when obj."valid" or obj."valid" is null then  'VALID'  else  'INVALID'
                end as status,
                'N' as temporary,
                null ::text as generated,
                null ::text as SECONDARY,
                (case OBJ.object_type when 'S' then  1  else  2 end) ::int1 as namespace,
                null ::text as EDITION_NAME,
                pkg.pkgowner as creator
            from gs_package PKG
            left join pg_type tp
                on (case
                    when pkg.isobject then
                    (pkg.pkgnamespace ::regnamespace ::text || '.' || pkg.pkgname) ::regtype ::oid
                end) = tp.oid
            left join pg_object obj
                on pkg.oid = obj.object_oid
            union all
            --pg_type 6.数据类型、自定义类型、集合类型
            select tp.typnamespace,
                tp.typname,
                null ::text as subobject_name,
                tp.oid,
                null as data_object_id,
                'TYPE' as object_type,
                null::timestamp(0) without time zone  as created,
                null::timestamp(0) without time zone  as last_ddl_time,
                null as timestamp,
                'VALID' as status,
                'N' as temporary,
                null ::text as generated,
                null ::text as SECONDARY,
                1 ::int1 as namespace,
                null ::text as EDITION_NAME,
                tp.typowner as creator
            from pg_type tp left join pg_class c
            on tp.typrelid =c.oid 
            where instantiable is null 
            and  nvl(relkind,'o') not in ('r','v','m','t','S','L')
            and (tp.typelem = 0 or typtype = 'o' )
            and instr(tp.typname,'.')=0
            );
            
            CREATE OR REPLACE VIEW compat_tools.USER_OBJECTS AS 
            SELECT object_name,
                   subobject_name,
                   object_id,data_object_id,
                   object_type,
                   created,
                   last_ddl_time,
                   "timestamp",
                   status,
                   "temporary",
                   "generated",
                   secondary,
                   "namespace",
                   edition_name,
                   creator 
                   FROM compat_tools.DBA_OBJECTS 
                   WHERE OWNER  = (case when current_schema()::text = lower(current_schema()::text) 
                                        then compat_tools.f_upper_name(current_schema()::text) 
                                        else current_schema()::text end);
        else
            -- 不含 Package 版本的视图
            CREATE OR REPLACE VIEW compat_tools.DBA_OBJECTS_LIST_WITHOUT_PKG
            AS
            SELECT relowner as creator      -- 1. 常规对象
                 , relnamespace AS owner
                 , relname AS object_name
                 , NULL as SUBOBJECT_NAME
                 , oid AS OBJECT_ID
                 , relfilenode as DATA_OBJECT_ID
                 , CASE relkind
                   WHEN 'r'::"char" THEN 'TABLE'::text
                   WHEN 'v'::"char" THEN 'VIEW'::text
                   WHEN 'm'::"char" THEN 'MATERIALIZED VIEW'::text
                   WHEN 'i'::"char" THEN 'INDEX'::text
                   WHEN 'I'::"char" THEN 'GLOBAL INDEX'::text
                   WHEN 'S'::"char" THEN 'SEQUENCE'::text
                   WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
                   WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
                   WHEN 't'::"char" THEN 'TOAST'::text
                   ELSE relkind::text
                   END AS object_type
                 , case when relpersistence = 't' THEN 'Y' else 'N' end as TEMPORARY
              FROM pg_catalog.pg_class
             UNION ALL
            SELECT CASE p.parttype WHEN 's' THEN st.relowner ELSE pp.relowner END as creator        -- 2. 分区/子分区对象
                 , CASE p.parttype WHEN 's' THEN st.relnamespace ELSE pp.relnamespace END AS owner
                 , CASE p.parttype WHEN 's' THEN st.relname ELSE pp.relname END AS object_name
                 , p.relname as SUBOBJECT_NAME
                 , p.oid AS OBJECT_ID
                 , p.relfilenode as DATA_OBJECT_ID
                 , CASE p.parttype
                        WHEN 'p'::"char" THEN 'TABLE PARTITION'::text
                        WHEN 'x'::"char" THEN (CASE it.parttype WHEN 'p' THEN 'INDEX PARTITION' ELSE 'INDEX SUBPARTITION' end)::text
                        WHEN 't'::"char" THEN 'TOAST PARTITION'::text
                        WHEN 's'::"char" THEN 'TABLE SUBPARTITION'::text
                        ELSE p.parttype::text
                    END AS object_type
                 , 'N' as TEMPORARY
              FROM pg_catalog.pg_partition as p  -- 分区/子分区
              left join pg_catalog.pg_class as pp on p.parentid = pp.oid  -- 分区的 parent
              left join pg_catalog.pg_partition as sp on p.parentid = sp.oid  -- 子分区的分区
              left join pg_catalog.pg_class as st on sp.parentid = st.oid  -- 子分区的分区的 parent
              left join pg_catalog.pg_partition as it on p.indextblid = it.oid  -- 分区索引的 table (分区/子分区)
             where p.parttype in ('p', 'x', 't', 's')
             UNION ALL
            SELECT proowner as creator      -- 3. 自定义函数（存储过程，函数，触发器）
                 , pronamespace AS owner
                 , proname AS object_name
                 , null as sub_object_name
                 , min(OID) as OBJECT_ID
                 , null as DATA_OBJECT_ID
                 , CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
                        ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
                   END AS object_type
                 , 'N' as temporary
              FROM pg_catalog.pg_proc
             WHERE pg_function_is_visible(oid)
             group by proowner, pronamespace, proname
                    , CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
                           ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
                      END
             UNION ALL
            SELECT synowner as creator      -- 4. 同义词
                 , synnamespace as owner
                 , synname as object_name
                 , null as sub_object_name
                 , oid as object_Id
                 , null as DATA_OBJECT_ID
                 , 'SYNONYM' as object_type
                 , 'N' as temporary
              from pg_catalog.pg_synonym;

            -- 根据是否有 gs_package  进行二次包装
            select count(*) into l_cnt
              from pg_class
             where relname = 'gs_package'
               and relnamespace = (select oid from pg_namespace where nspname = 'pg_catalog');
            if l_cnt = 0    -- 2.0 以及以下版本中，没有 gs_package 表
            then
                CREATE OR REPLACE VIEW compat_tools.DBA_OBJECTS
                AS
                SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS owner
                     , case when o1.object_name::text = lower(o1.object_name::text) then compat_tools.f_upper_name(o1.object_name::text) else o1.object_name::text end AS object_name
                     , case when o1.SUBOBJECT_NAME::text = lower(o1.SUBOBJECT_NAME::text) then compat_tools.f_upper_name(o1.SUBOBJECT_NAME::text) else o1.SUBOBJECT_NAME::text end AS SUBOBJECT_NAME
                     , o1.OBJECT_ID
                     , o1.DATA_OBJECT_ID
                     , o1.object_type
                     , o2.ctime as CREATED
                     , o2.mtime as LAST_DDL_TIME
                     , o1.TEMPORARY
                     , compat_tools.f_upper_name(u.usename) as creator
                  FROM compat_tools.dba_objects_list_without_pkg as o1
                  JOIN pg_catalog.pg_namespace as n on o1.owner = n.oid
                  JOIN pg_catalog.pg_user as u on o1.creator = u.usesysid
                  left JOIN pg_catalog.pg_object as o2 on o1.object_id = o2.object_oid
                 where n.nspname NOT LIKE 'pg_toast%';
                 CREATE OR REPLACE VIEW compat_tools.USER_OBJECTS AS SELECT object_name, subobject_name, object_id, data_object_id, object_type, temporary FROM compat_tools.DBA_OBJECTS WHERE OWNER  = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
            else    -- 2.1 以及以上版本中，还需要在 dba_objects_list_without_pkg 基础上，增加 package/package body 相关数据
                CREATE OR REPLACE VIEW compat_tools.DBA_OBJECTS AS
                select 
                case when "owner"::text = lower("owner"::text) then compat_tools.f_upper_name("owner"::text) else "owner"::text end AS owner
                                    , case when object_name::text = lower(object_name::text) then compat_tools.f_upper_name(object_name::text) else object_name::text end AS object_name
                                    , case when subobject_name::text = lower(subobject_name::text) then compat_tools.f_upper_name(subobject_name::text) else subobject_name::text end AS SUBOBJECT_NAME
                                    ,               
                object_id,data_object_id,object_type,created,last_ddl_time,"timestamp",
                status,"temporary","generated",secondary,"namespace",edition_name,
                compat_tools.f_upper_name(creator::text) as creator 
                from (
                    --pg_class 1. 表、视图、物化视图、索引、序列
                    select nsp.nspname as owner,
                    c.relname as object_name,
                    null ::text as subobject_name,
                    c.oid as object_id,
                    c.relfilenode as data_object_id,
                    CASE relkind
                        WHEN 'r' ::"char" THEN 'TABLE' ::text
                        WHEN 'v' ::"char" THEN 'VIEW' ::text
                        WHEN 'm' ::"char" THEN 'MATERIALIZED VIEW' ::text
                        WHEN 'i' ::"char" THEN 'INDEX' ::text
                        WHEN 'I' ::"char" THEN 'GLOBAL INDEX' ::text
                        WHEN 'S' ::"char" THEN 'SEQUENCE' ::text
                        WHEN 'f' ::"char" THEN 'FOREIGN TABLE' ::text
                        WHEN 'c' ::"char" THEN 'COMPOSITE TYPE' ::text
                        WHEN 't' ::"char" THEN 'TOAST' ::text
                        when 'L' ::"char" then 'LARGE SEQUENCE' ::text
                        ELSE  relkind ::text
                    END AS object_type,
                    obj.ctime::timestamp(0) without time zone  as created,
                    obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                    to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
                    case when c.relkind in ('v','m') then (case rr.ev_enabled when 'D' then 'INVALID' else 'VALID' END) else 'VALID' end  as STATUS,
                    case  when relpersistence in ('t', 'g') THEN  'Y'  else  'N'  end as temporary,
                    null ::text as generated,
                    null ::text as SECONDARY,
                    1 ::int1 as namespace,
                    null ::text as EDITION_NAME,
                    au.rolname  as creator
                from pg_class c
                left join pg_object obj
                    on obj.object_oid = c.oid
                left join pg_rewrite rr 
                    on c.oid=rr.ev_class 
                left join pg_namespace nsp
                    on nsp.oid=c.relnamespace
                left join pg_authid au
                    on au.oid=c.relowner
                union all
                --pg_partition  2. 分区/子分区对象
                SELECT nsp.nspname AS owner,
                    CASE p.parttype  WHEN 's' THEN  st.relname  ELSE  pp.relname END AS object_name,
                    p.relname as SUBOBJECT_NAME,
                    p.oid AS OBJECT_ID,
                    p.relfilenode as DATA_OBJECT_ID,
                    CASE p.parttype  
                        WHEN 'p' ::"char" THEN  'TABLE PARTITION' ::text
                        WHEN 'x' ::"char" THEN  (CASE it.parttype  WHEN 'p' THEN  'INDEX PARTITION'
                            ELSE  'INDEX SUBPARTITION'  end) ::text
                        WHEN 't' ::"char" THEN 'TOAST PARTITION' ::text
                        WHEN 's' ::"char" THEN  'TABLE SUBPARTITION' ::text
                        ELSE  p.parttype ::text
                    END AS object_type,
                    null ::timestamp(0) without time zone  as created,
                    null ::timestamp(0) without time zone  as last_ddl_time,
                    null ::text as timestamp,
                    'VALID' as status,
                    'N' as temporary,
                    null ::text as generated,
                    null ::text as SECONDARY,
                    1 ::int1 as namespace,
                    null ::text as EDITION_NAME,
                    au.rolname as creator 
                FROM pg_catalog.pg_partition as p -- 分区/子分区
                left join pg_catalog.pg_class as pp
                    on p.parentid = pp.oid -- 分区的 parent
                left join pg_catalog.pg_partition as sp
                    on p.parentid = sp.oid -- 子分区的分区
                left join pg_catalog.pg_class as st
                    on sp.parentid = st.oid -- 子分区的分区的 parent
                left join pg_catalog.pg_partition as it
                    on p.indextblid = it.oid -- 分区索引的 table (分区/子分区)
                left join pg_namespace nsp
                    on nsp.oid=CASE p.parttype WHEN 's' THEN  st.relnamespace  ELSE  pp.relnamespace end
                left join pg_authid au
                    on au.oid=CASE p.parttype
                        WHEN 's' THEN  st.relowner  ELSE  pp.relowner
                    END
                where p.parttype in ('p', 'x', 't', 's')
                union all
                --pg_proc 3.函数、过程、触发器
                SELECT nsp.nspname AS owner,
                    proname AS object_name,
                    null as sub_object_name,
                    p.OID as OBJECT_ID,
                    null as DATA_OBJECT_ID,
                    CASE
                        WHEN prorettype = 'trigger' ::regtype ::oid THEN  'TRIGGER' ::text
                        ELSE
                        (case prokind  when 'p' then  'PROCEDURE'  else  'FUNCTION'  end)
                    END AS object_type,
                    obj.ctime::timestamp(0) without time zone  as created,
                    obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                    to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
                    /*case
                        when obj."valid" or obj."valid" is null then  'VALID'  else  'INVALID'
                    end*/'VALID' as status,
                    'N' as temporary,
                    null ::text as generated,
                    null ::text as SECONDARY,
                    1 ::int1 as namespace,
                    null ::text as EDITION_NAME,
                    au.rolname as creator
                FROM pg_catalog.pg_proc p
                left join pg_object obj
                    on obj.object_oid = p.oid
                left join pg_namespace nsp
                    on nsp.oid=pronamespace
                left join pg_authid au
                    on au.oid=proowner
                WHERE propackageid = 0
                and ((p.prorettype <> 'trigger' ::regtype ::oid) 
                      or (p.prorettype = 'trigger' ::regtype ::oid 
                    and not exists (select 1 from pg_catalog.pg_trigger tri 
                                           where tri.tgfoid=p.oid 
                                           and nvl(jsonb_extract_path (row_to_json(tri)::jsonb,'tgfbody'),'null') <>'null'::jsonb))
                    )
                union all 
                --pg_proc O语法触发器
                select nsp.nspname AS owner,
                    tri.tgname AS object_name,
                    null as sub_object_name,
                    tri.OID as OBJECT_ID,
                    null as DATA_OBJECT_ID,
                    'TRIGGER'::text AS object_type,
                    obj.ctime::timestamp(0) without time zone  as created,
                    obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                    to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
                    /*case
                        when obj."valid" or obj."valid" is null then  'VALID'  else  'INVALID'
                    end*/'VALID' as status,
                    'N' as temporary,
                    null ::text as generated,
                    null ::text as SECONDARY,
                    3 ::int1 as namespace,
                    null ::text as EDITION_NAME,
                    au.rolname as creator
                from pg_catalog.pg_trigger tri
                left join pg_catalog.pg_object obj
                    on obj.object_oid=tri.oid
                left join pg_class cls
                    on tri.tgrelid=cls.oid
                left join pg_namespace nsp
                    on nsp.oid=relnamespace
                left join pg_authid au
                    on au.oid=tri.tgowner
                where nvl(jsonb_extract_path (row_to_json(tri)::jsonb,'tgfbody'),'null') <>'null'::jsonb
                union all
                --pg_synonym 4.同义词
                SELECT nsp.nspname as owner,
                    synname as object_name,
                    null as sub_object_name,
                    syn.oid as object_Id,
                    null as DATA_OBJECT_ID,
                    'SYNONYM' as object_type,
                    obj.ctime::timestamp(0) without time zone  as created,
                    obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                    to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
                    'VALID' as status,
                    'N' as temporary,
                    null ::text as generated,
                    null ::text as SECONDARY,
                    1 ::int1 as namespace,
                    null ::text as EDITION_NAME,
                    au.rolname as creator
                from pg_synonym syn
                left join pg_namespace nsp
                    on nsp.oid=synnamespace
                left join pg_authid au
                    on au.oid=synowner
                left join pg_object obj
                    on syn.oid = obj.object_oid and obj.object_type='n'
                union all
                --gs_package 5.plsql包、plsql类型
                select nsp.nspname,
                    pkg.pkgname,
                    null as sub_object_name,
                    /*case
                        when pkg.isobject then  tp.oid  else  pkg.oid
                    end*/pkg.oid as object_Id,
                    null as DATA_OBJECT_ID,
                    /*case
                        when pkg.isobject then  'TYPE'
                        else  'PACKAGE'
                    end*/'PACKAGE' || 
                    case OBJ.object_type  when 'B' then ' BODY'
                    end as object_type,
                    obj.ctime::timestamp(0) without time zone  as created,
                    obj.mtime::timestamp(0) without time zone  as last_ddl_time,
                    to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as timestamp,
                    /*case
                        when obj."valid" or obj."valid" is null then  'VALID'  else  'INVALID'
                    end*/'VALID' as status,
                    'N' as temporary,
                    null ::text as generated,
                    null ::text as SECONDARY,
                    (case OBJ.object_type when 'S' then  1  else  2 end) ::int1 as namespace,
                    null ::text as EDITION_NAME,
                    au.rolname as creator
                from gs_package PKG
                /*left join pg_type tp
                    on (case
                        when pkg.isobject then
                        (pkg.pkgnamespace ::regnamespace ::text || '.' || pkg.pkgname) ::regtype ::oid
                    end) = tp.oid*/
                left join pg_object obj
                    on pkg.oid = obj.object_oid
                left join pg_namespace nsp
                    on nsp.oid=pkg.pkgnamespace
                left join pg_authid au
                    on au.oid=pkg.pkgowner
                union all
                --pg_type 6.数据类型、自定义类型、集合类型
                select nsp.nspname,
                    tp.typname,
                    null ::text as subobject_name,
                    tp.oid,
                    null as data_object_id,
                    'TYPE' as object_type,
                    null::timestamp(0) without time zone  as created,
                    null::timestamp(0) without time zone  as last_ddl_time,
                    null as timestamp,
                    'VALID' as status,
                    'N' as temporary,
                    null ::text as generated,
                    null ::text as SECONDARY,
                    1 ::int1 as namespace,
                    null ::text as EDITION_NAME,
                    au.rolname as creator
                from pg_type tp left join pg_class c
                on tp.typrelid =c.oid 
                left join pg_namespace nsp
                    on nsp.oid=tp.typnamespace
                left join pg_authid au
                    on au.oid=tp.typowner
                where /*instantiable is null 
                and*/  nvl(relkind,'o') not in ('r','v','m','t','S','L')
                and (tp.typelem = 0 or typtype = 'o' )
                and instr(tp.typname,'.')=0
                );
                CREATE OR REPLACE VIEW compat_tools.USER_OBJECTS AS 
                SELECT object_name,
                    subobject_name,
                    object_id,data_object_id,
                    object_type,
                    created,
                    last_ddl_time,
                    "timestamp",
                    status,
                    "temporary",
                    "generated",
                    secondary,
                    "namespace",
                    edition_name,
                    creator 
                    FROM compat_tools.DBA_OBJECTS
                    WHERE OWNER  = (case when current_schema()::text = lower(current_schema()::text) 
                                            then compat_tools.f_upper_name(current_schema()::text) 
                                            else current_schema()::text end);
            end if;

            end if;
            
            CREATE OR REPLACE SYNONYM public.DBA_OBJECTS for compat_tools.DBA_OBJECTS;
            CREATE OR REPLACE SYNONYM public.ALL_OBJECTS for compat_tools.DBA_OBJECTS;
            CREATE OR REPLACE SYNONYM public.USER_OBJECTS for compat_tools.USER_OBJECTS;
            CREATE OR REPLACE SYNONYM public.OBJ for compat_tools.USER_OBJECTS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_OBJECTS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_CATALOG
    -- ALL_CATALOG
    -- USER_CATALOG
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_CATALOG', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_CATALOG
            AS
            SELECT owner
                 , object_name AS TABLE_NAME
                 , object_type as TABLE_TYPE
              FROM compat_tools.dba_objects c
             WHERE object_type in ('TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM');

            CREATE OR REPLACE VIEW compat_tools.USER_CATALOG AS SELECT TABLE_NAME, TABLE_TYPE FROM compat_tools.DBA_CATALOG WHERE OWNER  = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_CATALOG for compat_tools.DBA_CATALOG;
            CREATE OR REPLACE SYNONYM public.ALL_CATALOG for compat_tools.DBA_CATALOG;
            CREATE OR REPLACE SYNONYM public.USER_CATALOG for compat_tools.USER_CATALOG;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_CATALOG: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DICTIONARY    COMMENTS 字段均为空
    -- DICT
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DICTIONARY', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DICTIONARY
            AS
            SELECT OWNER
                 , OBJECT_NAME AS TABLE_NAME
                 , d.description as COMMENTS
              FROM compat_tools.dba_objects as o
              LEFT JOIN pg_catalog.pg_description d on o.object_id = d.objoid and d.objsubid = 0
             WHERE o.object_type in ('TABLE', 'VIEW')
               and o.owner in (
                compat_tools.f_upper_name('pg_catalog'),
                compat_tools.f_upper_name('information_schema'),
                compat_tools.f_upper_name('dbe_perf'),
                compat_tools.f_upper_name('pkg_service'),
                compat_tools.f_upper_name('snapshot'),
                compat_tools.f_upper_name('cstore'));

            CREATE OR REPLACE SYNONYM public.DICTIONARY for compat_tools.DICTIONARY;
            CREATE OR REPLACE SYNONYM public.DICT for compat_tools.DICTIONARY;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DICTIONARY: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_DEPENDENCIES
    -- ALL_DEPENDENCIES
    -- USER_DEPENDENCIES
    -- =========================================================================
    -- 假设： A 依赖 B
    -- ===========================
    --   DEPENDENCY_NORMAL    (n): A 删除不影响 B, B cascade 删除同步删除 A
    --   DEPENDENCY_AUTO      (a): 删除 B 自动删除 A (不管是 restrict 或 cascade)。例子: 约束对表的依赖
    --   DEPENDENCY_INTERNAL  (i): A 是 B 的一部分，不能单独删除 A, 删除 B 自动删除 A (无需 cascade)
    --   DEPENDENCY_EXTENSION (e): A 是 B extension 的一个成员。A 只能在 drop extension 的时候删除
    --   DEPENDENCY_PIN       (p): 系统本身依赖于被引用对象，决不能被删除。只在 initdb 时创建，A 是零
    -- =========================================================================
    -- 变更历史
    --    2022-06-29  1.1  修复不同对象拥有相同 oid 导致的查询数据不正确的问题
    --    2022-07-08  1.2  修复 gs_package 在低于 3.0 版本的数据库中不存在导致的报错
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_DEPENDENCIES', '2.0')
        then

            -- 根据是否有 gs_package  进行不同的视图创建
            select count(*) into l_cnt
              from pg_class
             where relname = 'gs_package'
               and relnamespace = (select oid from pg_namespace where nspname = 'pg_catalog');
            if l_cnt = 0    -- 2.0 以及以下版本中，没有 gs_package 表
            then
                CREATE OR REPLACE VIEW compat_tools.DBA_DEPENDENCIES
                AS
                with obj as (select obj.oid, obj.pg_type, obj.object_type, obj.object_name, nsp.nspname as schema_name
                            from (select 'pg_synonym' as pg_type, 'SYNONYM' as object_type, synname as object_name, synnamespace as object_nsp, oid from pg_synonym
                                    union all
                                    select 'pg_class', CASE relkind WHEN 'r'::"char" THEN 'TABLE'::text
                                                                    WHEN 'v'::"char" THEN 'VIEW'::text
                                                                    WHEN 'm'::"char" THEN 'MATERIALIZED VIEW'::text
                                                                    WHEN 'i'::"char" THEN 'INDEX'::text
                                                                    WHEN 'I'::"char" THEN 'GLOBAL INDEX'::text
                                                                    WHEN 'S'::"char" THEN 'SEQUENCE'::text
                                                                    WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
                                                                    WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
                                                                    WHEN 't'::"char" THEN 'TOAST'::text
                                                                    ELSE relkind::text
                                                                    END, relname, relnamespace, oid from pg_class
                                    union all
                                    select 'pg_ts_parser', 'TS_PARSER', prsname, prsnamespace, oid from pg_ts_parser
                                    union all
                                    select 'pg_collation', 'COLLATION', collname, collnamespace, oid from pg_collation
                                    union all
                                    select 'pg_constraint', 'CONSTRAINT', conname, connamespace, oid from pg_constraint
                                    union all
                                    select 'pg_proc', CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'
                                                            ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
                                                    END, proname, pronamespace, oid from pg_proc
                                    union all
                                    select 'pg_opfamily', 'OP_FAMILY', opfname, opfnamespace, oid from pg_opfamily
                                    union all
                                    select 'pg_type', 'TYPE', typname, typnamespace, oid from pg_type
                                    union all
                                    select 'pg_opclass', 'OP_CLASS', opcname, opcnamespace, oid from pg_opclass
                                    union all
                                    select 'pg_conversion', 'CONVERSION', conname, connamespace, oid from pg_conversion
                                    union all
                                    select 'pg_ts_template', 'TS_TEMPLATE', tmplname, tmplnamespace, oid from pg_ts_template
                                    union all
                                    select 'pg_ts_config', 'TS_CONFIG', cfgname, cfgnamespace, oid from pg_ts_config
                                    union all
                                    select 'pg_extension', 'EXTENSION', extname, extnamespace, oid from pg_extension
                                    union all
                                    select 'pg_operator', 'OPERATOR', oprname, oprnamespace, oid from pg_operator
                                    union all
                                    select 'pg_namespace', 'SCHEMA', nspname, oid, oid from pg_namespace
                                    union all
                                    select 'pg_ts_dict', 'TS_DICT', dictname, dictnamespace, oid from pg_ts_dict
                                    union all
                                    select 'pg_rewrite', 'REWRITE', cls.relname, cls.relnamespace, rwt.oid
                                    from pg_rewrite as rwt
                                    join pg_class as cls on rwt.ev_class = cls.oid
                                    ) as obj
                            join pg_namespace as nsp on obj.object_nsp = nsp.oid
                            union all
                            select obj.oid, obj.pg_type, obj.object_type, obj.object_name, own.rolname as schema_name
                            from (select 'pg_language' as pg_type, 'LANGUAGE' as object_type, lanname as object_name, lanowner as owner_oid, oid from pg_language
                                    union all
                                    select 'pg_foreign_server', 'FOREIGN SERVER', srvname, srvowner, oid from pg_foreign_server
                                    union all
                                    select 'pg_foreign_data_wrapper', 'FOREIGN DATA WRAPPER', fdwname, fdwowner, oid from pg_foreign_data_wrapper
                                    ) as obj
                            join pg_authid as own on obj.owner_oid = own.oid)
                SELECT case when owner = lower(owner) then compat_tools.f_upper_name(owner) else owner end AS owner
                    , case when NAME = lower(NAME) then compat_tools.f_upper_name(NAME) else NAME end AS NAME
                    , TYPE
                    , case when referenced_owner = lower(referenced_owner) then compat_tools.f_upper_name(referenced_owner) else referenced_owner end AS referenced_owner
                    , case when referenced_name = lower(referenced_name) then compat_tools.f_upper_name(referenced_name) else referenced_name end AS referenced_name
                    , REFERENCED_TYPE
                    , DEPENDENCY_TYPE
                FROM (select distinct obj.schema_name as owner
                            , coalesce(obj.object_name, d.objid::text) as NAME
                            , coalesce(obj.object_type, cls.relname::text) as TYPE
                            , ref_obj.schema_name as REFERENCED_OWNER
                            , coalesce(ref_obj.object_name, d.refobjid::text) as REFERENCED_NAME
                            , coalesce(ref_obj.object_type, ref_cls.relname::text) as REFERENCED_TYPE
                            , case d.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else compat_tools.f_upper_name(d.deptype::text) end as DEPENDENCY_TYPE
                        FROM pg_catalog.pg_depend d
                        join pg_catalog.pg_class as cls on d.classid = cls.oid
                        join pg_catalog.pg_class as ref_cls on d.refclassid = ref_cls.oid
                        left join obj on d.objid = obj.oid and cls.relname = obj.pg_type
                        left join obj as ref_obj on d.refobjid = ref_obj.oid and ref_cls.relname = ref_obj.pg_type
                        WHERE d.objsubid = 0
                    ) as o
                WHERE owner||'.'||name != referenced_owner||'.'||referenced_name;
            else
                CREATE OR REPLACE VIEW compat_tools.DBA_DEPENDENCIES
                AS
                with obj as (select obj.oid, obj.pg_type, obj.object_type, obj.object_name, nsp.nspname as schema_name
                            from (select 'pg_synonym' as pg_type, 'SYNONYM' as object_type, synname as object_name, synnamespace as object_nsp, oid from pg_synonym
                                    union all
                                    select 'pg_class', CASE relkind WHEN 'r'::"char" THEN 'TABLE'::text
                                                                    WHEN 'v'::"char" THEN 'VIEW'::text
                                                                    WHEN 'm'::"char" THEN 'MATERIALIZED VIEW'::text
                                                                    WHEN 'i'::"char" THEN 'INDEX'::text
                                                                    WHEN 'I'::"char" THEN 'GLOBAL INDEX'::text
                                                                    WHEN 'S'::"char" THEN 'SEQUENCE'::text
                                                                    WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
                                                                    WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
                                                                    WHEN 't'::"char" THEN 'TOAST'::text
                                                                    ELSE relkind::text
                                                                    END, relname, relnamespace, oid from pg_class
                                    union all
                                    select 'pg_ts_parser', 'TS_PARSER', prsname, prsnamespace, oid from pg_ts_parser
                                    union all
                                    select 'pg_collation', 'COLLATION', collname, collnamespace, oid from pg_collation
                                    union all
                                    select 'pg_constraint', 'CONSTRAINT', conname, connamespace, oid from pg_constraint
                                    union all
                                    select 'pg_proc', CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'
                                                            ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
                                                    END, proname, pronamespace, oid from pg_proc
                                    union all
                                    select 'pg_opfamily', 'OP_FAMILY', opfname, opfnamespace, oid from pg_opfamily
                                    union all
                                    select 'pg_type', 'TYPE', typname, typnamespace, oid from pg_type
                                    union all
                                    select 'pg_opclass', 'OP_CLASS', opcname, opcnamespace, oid from pg_opclass
                                    union all
                                    select 'pg_conversion', 'CONVERSION', conname, connamespace, oid from pg_conversion
                                    union all
                                    select 'pg_ts_template', 'TS_TEMPLATE', tmplname, tmplnamespace, oid from pg_ts_template
                                    union all
                                    select 'pg_ts_config', 'TS_CONFIG', cfgname, cfgnamespace, oid from pg_ts_config
                                    union all
                                    select 'pg_extension', 'EXTENSION', extname, extnamespace, oid from pg_extension
                                    union all
                                    select 'pg_operator', 'OPERATOR', oprname, oprnamespace, oid from pg_operator
                                    union all
                                    select 'pg_namespace', 'SCHEMA', nspname, oid, oid from pg_namespace
                                    union all
                                    select 'pg_ts_dict', 'TS_DICT', dictname, dictnamespace, oid from pg_ts_dict
                                    union all
                                    select 'pg_rewrite', 'REWRITE', cls.relname, cls.relnamespace, rwt.oid
                                    from pg_rewrite as rwt
                                    join pg_class as cls on rwt.ev_class = cls.oid
                                    union all
                                    select 'gs_package', 'PACKAGE', pkgname, pkgnamespace, oid from gs_package
                                    ) as obj
                            join pg_namespace as nsp on obj.object_nsp = nsp.oid
                            union all
                            select obj.oid, obj.pg_type, obj.object_type, obj.object_name, own.rolname as schema_name
                            from (select 'pg_language' as pg_type, 'LANGUAGE' as object_type, lanname as object_name, lanowner as owner_oid, oid from pg_language
                                    union all
                                    select 'pg_foreign_server', 'FOREIGN SERVER', srvname, srvowner, oid from pg_foreign_server
                                    union all
                                    select 'pg_foreign_data_wrapper', 'FOREIGN DATA WRAPPER', fdwname, fdwowner, oid from pg_foreign_data_wrapper
                                    ) as obj
                            join pg_authid as own on obj.owner_oid = own.oid)
                SELECT case when owner = lower(owner) then compat_tools.f_upper_name(owner) else owner end AS owner
                    , case when NAME = lower(NAME) then compat_tools.f_upper_name(NAME) else NAME end AS NAME
                    , TYPE
                    , case when referenced_owner = lower(referenced_owner) then compat_tools.f_upper_name(referenced_owner) else referenced_owner end AS referenced_owner
                    , case when referenced_name = lower(referenced_name) then compat_tools.f_upper_name(referenced_name) else referenced_name end AS referenced_name
                    , REFERENCED_TYPE
                    , DEPENDENCY_TYPE
                FROM (select distinct obj.schema_name as owner
                            , coalesce(obj.object_name, d.objid::text) as NAME
                            , coalesce(obj.object_type, cls.relname::text) as TYPE
                            , ref_obj.schema_name as REFERENCED_OWNER
                            , coalesce(ref_obj.object_name, d.refobjid::text) as REFERENCED_NAME
                            , coalesce(ref_obj.object_type, ref_cls.relname::text) as REFERENCED_TYPE
                            , case d.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else compat_tools.f_upper_name(d.deptype::text) end as DEPENDENCY_TYPE
                        FROM pg_catalog.pg_depend d
                        join pg_catalog.pg_class as cls on d.classid = cls.oid
                        join pg_catalog.pg_class as ref_cls on d.refclassid = ref_cls.oid
                        left join obj on d.objid = obj.oid and cls.relname = obj.pg_type
                        left join obj as ref_obj on d.refobjid = ref_obj.oid and ref_cls.relname = ref_obj.pg_type
                        WHERE d.objsubid = 0
                    ) as o
                WHERE owner||'.'||name != referenced_owner||'.'||referenced_name;
            end if;

            CREATE OR REPLACE VIEW compat_tools.USER_DEPENDENCIES AS SELECT NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, DEPENDENCY_TYPE FROM compat_tools.DBA_DEPENDENCIES WHERE OWNER  = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_DEPENDENCIES for compat_tools.DBA_DEPENDENCIES;
            CREATE OR REPLACE SYNONYM public.ALL_DEPENDENCIES for compat_tools.DBA_DEPENDENCIES;
            CREATE OR REPLACE SYNONYM public.USER_DEPENDENCIES for compat_tools.USER_DEPENDENCIES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_DEPENDENCIES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_SEGMENTS
    -- ALL_SEGMENTS
    -- USER_SEGMENTS
    -- =========================================================================
    -- 2022-01-25  [1.0 -> 1.1]    调整表分区/子分区的大小计算，删除索引分区大小的计算
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_SEGMENTS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_SEGMENTS
            AS
            SELECT o.owner
                 , o.object_name as SEGMENT_NAME
                 , o.subobject_name as PARTITION_NAME
                 , o.object_type as SEGMENT_TYPE
                 , compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
                 , (case o.object_type
                    when 'TABLE' then pg_table_size(o.object_id)
                    when 'INDEX' then pg_table_size(o.object_id)
                    when 'TABLE PARTITION' then pg_partition_size(p.parentid, p.oid)
                    -- when 'INDEX PARTITION' then pg_partition_indexes_size(ip.parentid, ip.oid)
                    when 'TABLE SUBPARTITION' then pg_partition_size(sp.parentid, p.oid)
                    -- when 'INDEX SUBPARTITION' then pg_partition_indexes_size(isp.parentid, ip.oid)
                    else pg_relation_size(o.data_object_id)
                    end) as bytes
                 , (case o.object_type
                    when 'TABLE' then pg_table_size(o.object_id)
                    when 'INDEX' then pg_table_size(o.object_id)
                    when 'TABLE PARTITION' then pg_partition_size(p.parentid, p.oid)
                    -- when 'INDEX PARTITION' then pg_partition_indexes_size(ip.parentid, ip.oid)
                    when 'TABLE SUBPARTITION' then pg_partition_size(sp.parentid, p.oid)
                    -- when 'INDEX SUBPARTITION' then pg_partition_indexes_size(isp.parentid, isp.oid)
                    else pg_relation_size(o.data_object_id)
                    end / b.block_size)::bigint as blocks
              FROM compat_tools.DBA_OBJECTS o
              join (select setting::bigint as block_size FROM pg_catalog.pg_settings WHERE name = 'block_size') b on 1=1
              left join pg_catalog.pg_class c on o.object_id = c.oid
              left join pg_catalog.pg_partition p on o.object_id = p.oid  -- 分区表/索引对应的分区信息
              left join pg_catalog.pg_partition sp on sp.oid = p.parentid  -- 分区表/索引对应的子分区的上层分区
              left join pg_catalog.pg_tablespace t on c.reltablespace = t.oid or p.reltablespace = t.oid
              left join pg_catalog.pg_partition ip on p.indextblid = ip.oid  -- 索引对应的表的分区/子分区
              left join pg_catalog.pg_partition isp on ip.parentid = isp.oid  -- 索引对应的表的子分区的父表
             WHERE o.object_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION');

            CREATE OR REPLACE VIEW compat_tools.USER_SEGMENTS AS SELECT segment_name, partition_name, segment_type, tablespace_name, bytes, blocks FROM compat_tools.DBA_SEGMENTS WHERE OWNER  = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_SEGMENTS for compat_tools.DBA_SEGMENTS;
            CREATE OR REPLACE SYNONYM public.ALL_SEGMENTS for compat_tools.DBA_SEGMENTS;
            CREATE OR REPLACE SYNONYM public.USER_SEGMENTS for compat_tools.USER_SEGMENTS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_SEGMENTS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_SOURCE_ALL
    -- DBA_SOURCE
    -- ALL_SOURCE
    -- USER_SOURCE
    -- =========================================================================
    -- 2025-02-26  [2.0 -> 2.1]    修改 DBA_SOURCE_ALL 视图，增加对 O语法触发器 的支持
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_SOURCE_ALL', '2.1')
        then
        if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.0.9') = 1 then
            CREATE OR REPLACE VIEW compat_tools.DBA_SOURCE_ALL
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when p.proname::text = lower(p.proname::text) then compat_tools.f_upper_name(p.proname::text) else p.proname::text end AS NAME
                 , CASE WHEN p.prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
                        ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
                   END AS TYPE
                 , length(p.prosrc) - length(replace(p.prosrc, chr(10), '')) + 1 as lines
                 , p.prosrc as TEXT
              FROM pg_catalog.pg_proc as p
              JOIN pg_catalog.pg_namespace as n on p.pronamespace = n.oid
             WHERE p.prolang not in (select oid FROM pg_catalog.pg_language WHERE lanname in ('internal', 'c'))
              and ((p.prorettype <> 'trigger' ::regtype ::oid) 
                      or (p.prorettype = 'trigger' ::regtype ::oid 
                    and not exists (select 1 from pg_catalog.pg_trigger tri 
                                           where tri.tgfoid=p.oid 
                                           and nvl(jsonb_extract_path (row_to_json(tri)::jsonb,'tgfbody'),'null') <>'null'::jsonb))
                  )
              union all 
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when tri.tgname::text = lower(tri.tgname::text) then compat_tools.f_upper_name(tri.tgname::text) else tri.tgname::text end AS NAME
                 , 'TRIGGER'::text AS TYPE
                 , length(tri.prosrc) - length(replace(tri.prosrc, chr(10), '')) + 1 as lines
                 , tri.prosrc as TEXT
              FROM (select oid,tgname,tgrelid,pg_get_triggerdef(tri.oid) prosrc from pg_catalog.pg_trigger tri 
                    where nvl(jsonb_extract_path (row_to_json(tri)::jsonb,'tgfbody'),'null') <>'null'::jsonb) tri
              join pg_catalog.pg_class as cls on tri.tgrelid=cls.oid
              JOIN pg_catalog.pg_namespace as n on cls.relnamespace = n.oid  ;
        ELSE
            CREATE OR REPLACE VIEW compat_tools.DBA_SOURCE_ALL
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when p.proname::text = lower(p.proname::text) then compat_tools.f_upper_name(p.proname::text) else p.proname::text end AS NAME
                 , CASE WHEN p.prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
                        ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
                   END AS TYPE
                 , length(p.prosrc) - length(replace(p.prosrc, chr(10), '')) + 1 as lines
                 , p.prosrc as TEXT
              FROM pg_catalog.pg_proc as p
              JOIN pg_catalog.pg_namespace as n on p.pronamespace = n.oid
             WHERE p.prolang not in (select oid FROM pg_catalog.pg_language WHERE lanname in ('internal', 'c'));
        end if;

            CREATE OR REPLACE VIEW compat_tools.DBA_SOURCE
            AS
            with recursive t_line as (select owner, name, type, text, 1 as line_id, lines FROM compat_tools.DBA_SOURCE_ALL 
                                       union all
                                      select owner, name, type, text, 1 + line_id as line_id, lines FROM t_line WHERE 1 + line_id <= lines)
            select owner, name, type, line_id as line, p1,p2,rtrim(substr(text, p1 + 1, p2-p1),chr(10)) as text 
              FROM (select owner, name, type, line_id, lines, text
                         , case line_id when 1 then 0 else instr(text, chr(10), 1, line_id - 1) end as p1
                         , case instr(text, chr(10), 1, line_id) when 0 then length(text) else instr(text, chr(10), 1, line_id) end as p2
                      FROM t_line) as x
             order by owner, name, line_id;

            CREATE OR REPLACE VIEW compat_tools.USER_SOURCE AS SELECT name, type, line, text FROM compat_tools.DBA_SOURCE WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_SOURCE_ALL for compat_tools.DBA_SOURCE_ALL;
            CREATE OR REPLACE SYNONYM public.DBA_SOURCE for compat_tools.DBA_SOURCE;
            CREATE OR REPLACE SYNONYM public.ALL_SOURCE for compat_tools.DBA_SOURCE;
            CREATE OR REPLACE SYNONYM public.USER_SOURCE for compat_tools.USER_SOURCE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_SOURCE_ALL: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_PROCEDURES
    -- ALL_PROCEDURES
    -- USER_PROCEDURES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_PROCEDURES', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_PROCEDURES
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when p.proname::text = lower(p.proname::text) then compat_tools.f_upper_name(p.proname::text) else p.proname::text end AS OBJECT_NAME
                 , p.oid as OBJECT_ID
                 , CASE WHEN p.prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
                        ELSE (case p.prokind when 'p' then 'PROCEDURE' when 'f' then 'FUNCTION' else NULL end)
                   END AS OBJECT_TYPE
                 , case p.prokind when 'a' then 'YES' else 'NO' end as AGGREGATE
                 , case p.provolatile when 'v' then 'NO' else 'YES 'end as DETERMINISTIC
              FROM pg_catalog.pg_proc as p
              JOIN pg_catalog.pg_namespace as n on p.pronamespace = n.oid
             WHERE p.prolang not in (select oid FROM pg_catalog.pg_language WHERE lanname in ('internal', 'c'));

            CREATE OR REPLACE VIEW compat_tools.USER_PROCEDURES AS SELECT object_name, object_id, object_type, aggregate, deterministic FROM compat_tools.DBA_PROCEDURES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_PROCEDURES for compat_tools.DBA_PROCEDURES;
            CREATE OR REPLACE SYNONYM public.ALL_PROCEDURES for compat_tools.DBA_PROCEDURES;
            CREATE OR REPLACE SYNONYM public.USER_PROCEDURES for compat_tools.USER_PROCEDURES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_PROCEDURES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TRIGGERS
    -- ALL_TRIGGERS
    -- USER_TRIGGERS
    -- =========================================================================
    -- 2023-11-09  [2.0 -> 2.1]    修复不同schema下有同名trigger导致有重复数据的情况
    -- 2023-11-11  [2.1 -> 2.2]    重写dba_triggers视图，合并event为一行,增加支持truncate事件的显示，支持识别基表类型为view
    -- 2025-03-19  [2.2 -> 3.0]    重写dba_triggers视图，对齐ORACLE19C,支持匿名块触发器,修复触发器类型显示错误的问题
    -- 2025-03-21  [3.0 -> 4.0]    区分3.0版本以上和以下的数据库，对视图进行不同的创建
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TRIGGERS', '4.0')
        then
         if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.9.9') = 1 then
            create or replace view compat_tools.DBA_TRIGGERS as 
            SELECT
                CASE WHEN owner= lower(owner) THEN compat_tools.f_upper_name(owner) END AS owner,
                CASE WHEN trigger_name= lower(trigger_name) THEN compat_tools.f_upper_name(trigger_name) END AS trigger_name,
                compat_tools.f_upper_name(trigger_type) AS trigger_type,
                compat_tools.f_upper_name(triggering_event) AS triggering_event,
                CASE WHEN table_owner= lower(table_owner) THEN compat_tools.f_upper_name(table_owner) END AS table_owner,
                base_object_type,
                CASE WHEN table_name= lower(table_name) THEN compat_tools.f_upper_name(table_name) END AS table_name,
                column_name,
                referencing_names,
                when_clause,
                status,
                description,
                action_type,
                trigger_body,
                crossedition,
                before_statement,
                before_row,
                after_row,
                after_statement,
                instead_of_row,
                fire_once,
                apply_server_only
            FROM (
                SELECT 
                    n.nspname as owner,
                    t.tgname as trigger_name,
                    CASE t.tgtype::integer & 66
                        WHEN 2 THEN 'BEFORE'::text
                        WHEN 64 THEN 'INSTEAD OF'::text
                        ELSE 'AFTER'::text
                    END||' '||CASE t.tgtype & 1 WHEN 1 THEN 'ROW'::text ELSE 'STATEMENT'::text END as trigger_type,
                    ltrim(CONCAT(
                      case  (t.tgtype::INT4 & 4)::BOOL when TRUE then ' OR INSERT' end,
                      case  (t.tgtype::INT4 & 16)::BOOL when TRUE then ' OR UPDATE' end,
                      case  (t.tgtype::INT4 & 8)::BOOL when TRUE then ' OR DELETE' end,
                      case  (t.tgtype::INT4 & 32)::BOOL when TRUE then ' OR TRUNCATE' end
                    ),' OR ') as triggering_event,
                    n.nspname as table_owner,
                    CASE
                        WHEN c.relkind ='v' THEN 'VIEW'
                        WHEN c.relkind IN ('r','f') THEN 'TABLE'
                        ELSE 'UNDEFINED'
                    END as base_object_type,
                    c.relname as table_name,
                    null::text as column_name,
                    'REFERENCING NEW AS NEW OLD AS OLD' as referencing_names,
                    CASE
                        WHEN nvl(jsonb_extract_path(row_to_json(t)::jsonb,'tgfbody'),'null') ='null'::jsonb THEN (
                            SELECT rm.m[1] AS m
                            FROM regexp_matches(pg_get_triggerdef(t.oid), '.{35,} WHEN \((.+)\) EXECUTE PROCEDURE'::text) rm(m)
                            LIMIT 1
                        )
                        ELSE (
                            SELECT rm.m[1] AS m
                            FROM regexp_matches(pg_get_triggerdef(t.oid), '(?i).{35,} WHEN \((.+)\)\s+declare'::text) rm(m)
                            LIMIT 1
                        )::text
                    END as when_clause,
                    CASE t.tgenabled
                        WHEN 'D'::"char" THEN 'DISABLED'::text
                        ELSE 'ENABLED'::text
                    END as status,
                    CASE
                        WHEN nvl(jsonb_extract_path(row_to_json(t)::jsonb,'tgfbody'),'null') ='null'::jsonb
                        THEN substr(pg_get_triggerdef(t.oid),16,"position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text)+47-16)
                        ELSE --substr(pg_get_triggerdef(t.oid),16,regexp_instr("substring"(pg_get_triggerdef(t.oid), 48), '(?i)\sdeclare')+47-16)
                     (
                            SELECT rm.m[1] AS m
                            FROM regexp_matches(pg_get_triggerdef(t.oid), '(?i)CREATE .+TRIGGER (.+)\s+declare'::text) rm(m)
                            LIMIT 1
                        )
                    END as description,
                    CASE
                        WHEN nvl(jsonb_extract_path(row_to_json(t)::jsonb,'tgfbody'),'null') ='null'::jsonb THEN 'CALL'
                        ELSE 'PL/SQL'
                    END as action_type,
                    CASE
                        WHEN nvl(jsonb_extract_path(row_to_json(t)::jsonb,'tgfbody'),'null') <>'null'::jsonb THEN
                            --"substring"(pg_get_triggerdef(t.oid), regexp_instr("substring"(pg_get_triggerdef(t.oid), 48), '(?i)\sdeclare') + 48)
                        (
                            SELECT rm.m[1] AS m
                            FROM regexp_matches(pg_get_triggerdef(t.oid), '(?i)CREATE .+TRIGGER .+\s+(declare.+)'::text) rm(m)
                            LIMIT 1
                        )
                        ELSE
                            "substring"(pg_get_triggerdef(t.oid), "position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text) + 47)
                    END as trigger_body,
                    'NO' crossedition,
                    CASE WHEN t.tgtype::integer & 1 <> 1 AND t.tgtype::integer & 66 = 2 THEN 'YES' END as before_statement,
                    CASE WHEN t.tgtype::integer & 1 = 1 AND t.tgtype::integer & 66 = 2 THEN 'YES' END as before_row,
                    CASE WHEN t.tgtype::integer & 1 = 1 AND t.tgtype::integer & 66 NOT IN (2,64) THEN 'YES' END as after_row,
                    CASE WHEN t.tgtype::integer & 1 <> 1 AND t.tgtype::integer & 66 NOT IN (2,64) THEN 'YES' END as after_statement,
                    CASE WHEN t.tgtype::integer & 1 = 1 AND t.tgtype::integer & 66 = 64 THEN 'YES' END as instead_of_row,
                    CASE t.tgenabled when 'O' then 'YES' else 'NO' END as fire_once,
                    CASE t.tgenabled when 'R' then 'YES' else 'NO' END as apply_server_only
                FROM pg_namespace n, pg_class c, pg_trigger t
                WHERE n.oid = c.relnamespace
                    AND c.oid = t.tgrelid
                    AND NOT t.tgisinternal
                    AND NOT pg_is_other_temp_schema(n.oid)
            ) it;

            create or replace view compat_tools.ALL_TRIGGERS as 
            SELECT
                CASE WHEN owner= lower(owner) THEN compat_tools.f_upper_name(owner) END AS owner,
                CASE WHEN trigger_name= lower(trigger_name) THEN compat_tools.f_upper_name(trigger_name) END AS trigger_name,
                compat_tools.f_upper_name(trigger_type) AS trigger_type,
                compat_tools.f_upper_name(triggering_event) AS triggering_event,
                CASE WHEN table_owner= lower(table_owner) THEN compat_tools.f_upper_name(table_owner) END AS table_owner,
                base_object_type,
                CASE WHEN table_name= lower(table_name) THEN compat_tools.f_upper_name(table_name) END AS table_name,
                column_name,
                referencing_names,
                when_clause,
                status,
                description,
                action_type,
                trigger_body,
                crossedition,
                before_statement,
                before_row,
                after_row,
                after_statement,
                instead_of_row,
                fire_once,
                apply_server_only
            FROM (
                SELECT 
                    n.nspname as owner,
                    t.tgname as trigger_name,
                    CASE t.tgtype::integer & 66
                        WHEN 2 THEN 'BEFORE'::text
                        WHEN 64 THEN 'INSTEAD OF'::text
                        ELSE 'AFTER'::text
                    END||' '||CASE t.tgtype & 1 WHEN 1 THEN 'ROW'::text ELSE 'STATEMENT'::text END as trigger_type,
                    ltrim(CONCAT(
                      case  (t.tgtype::INT4 & 4)::BOOL when TRUE then ' OR INSERT' end,
                      case  (t.tgtype::INT4 & 16)::BOOL when TRUE then ' OR UPDATE' end,
                      case  (t.tgtype::INT4 & 8)::BOOL when TRUE then ' OR DELETE' end,
                      case  (t.tgtype::INT4 & 32)::BOOL when TRUE then ' OR TRUNCATE' end
                    ),' OR ') as triggering_event,
                    n.nspname as table_owner,
                    CASE
                        WHEN c.relkind ='v' THEN 'VIEW'
                        WHEN c.relkind IN ('r','f') THEN 'TABLE'
                        ELSE 'UNDEFINED'
                    END as base_object_type,
                    null::text as column_name,
                    c.relname as table_name,
                    'REFERENCING NEW AS NEW OLD AS OLD' as referencing_names,
                    CASE
                        WHEN nvl(jsonb_extract_path(row_to_json(t)::jsonb,'tgfbody'),'null') ='null'::jsonb THEN (
                            SELECT rm.m[1] AS m
                            FROM regexp_matches(pg_get_triggerdef(t.oid), '.{35,} WHEN \((.+)\) EXECUTE PROCEDURE'::text) rm(m)
                            LIMIT 1
                        )
                        ELSE (
                            SELECT rm.m[1] AS m
                            FROM regexp_matches(pg_get_triggerdef(t.oid), '(?i).{35,} WHEN \((.+)\)\s+declare'::text) rm(m)
                            LIMIT 1
                        )::text
                    END as when_clause,
                    CASE t.tgenabled
                        WHEN 'D'::"char" THEN 'DISABLED'::text
                        ELSE 'ENABLED'::text
                    END as status,
                    CASE
                        WHEN nvl(jsonb_extract_path(row_to_json(t)::jsonb,'tgfbody'),'null') ='null'::jsonb
                        THEN substr(pg_get_triggerdef(t.oid),16,"position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text)+47-16)
                        ELSE --substr(pg_get_triggerdef(t.oid),16,regexp_instr("substring"(pg_get_triggerdef(t.oid), 48), '(?i)\sdeclare')+47-16)
                     (
                            SELECT rm.m[1] AS m
                            FROM regexp_matches(pg_get_triggerdef(t.oid), '(?i)CREATE .+TRIGGER (.+)\s+declare'::text) rm(m)
                            LIMIT 1
                        )
                    END as description,
                    CASE
                        WHEN nvl(jsonb_extract_path(row_to_json(t)::jsonb,'tgfbody'),'null') ='null'::jsonb THEN 'CALL'
                        ELSE 'PL/SQL'
                    END as action_type,
                    CASE
                        WHEN nvl(jsonb_extract_path(row_to_json(t)::jsonb,'tgfbody'),'null') <>'null'::jsonb THEN
                            --"substring"(pg_get_triggerdef(t.oid), regexp_instr("substring"(pg_get_triggerdef(t.oid), 48), '(?i)\sdeclare') + 48)
                        (
                            SELECT rm.m[1] AS m
                            FROM regexp_matches(pg_get_triggerdef(t.oid), '(?i)CREATE .+TRIGGER .+\s+(declare.+)'::text) rm(m)
                            LIMIT 1
                        )
                        ELSE
                            "substring"(pg_get_triggerdef(t.oid), "position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text) + 47)
                    END as trigger_body,
                    'NO' crossedition,
                    CASE WHEN t.tgtype::integer & 1 <> 1 AND t.tgtype::integer & 66 = 2 THEN 'YES' END as before_statement,
                    CASE WHEN t.tgtype::integer & 1 = 1 AND t.tgtype::integer & 66 = 2 THEN 'YES' END as before_row,
                    CASE WHEN t.tgtype::integer & 1 = 1 AND t.tgtype::integer & 66 NOT IN (2,64) THEN 'YES' END as after_row,
                    CASE WHEN t.tgtype::integer & 1 <> 1 AND t.tgtype::integer & 66 NOT IN (2,64) THEN 'YES' END as after_statement,
                    CASE WHEN t.tgtype::integer & 1 = 1 AND t.tgtype::integer & 66 = 64 THEN 'YES' END as instead_of_row,
                    CASE t.tgenabled when 'O' then 'YES' else 'NO' END as fire_once,
                    CASE t.tgenabled when 'R' then 'YES' else 'NO' END as apply_server_only
                FROM pg_namespace n, pg_class c, pg_trigger t
                    WHERE n.oid = c.relnamespace 
                        AND c.oid = t.tgrelid
                        AND NOT t.tgisinternal 
                        AND NOT pg_is_other_temp_schema(n.oid)
                        and (pg_has_role(c.relowner, 'USAGE'::text) 
                            OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) 
                            OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES'::text))
                ) it;

            CREATE OR REPLACE VIEW compat_tools.USER_TRIGGERS AS 
             SELECT trigger_name,trigger_type,triggering_event,table_owner,
                    base_object_type,table_name,column_name,referencing_names,when_clause,
                    status,description,action_type,trigger_body,crossedition,
                    before_statement,before_row,after_row,after_statement,instead_of_row,
                    fire_once,apply_server_only FROM compat_tools.DBA_TRIGGERS 
                    WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) 
                                        then compat_tools.f_upper_name(current_schema()::text) 
                                        else current_schema()::text 
                                   end);

            CREATE OR REPLACE SYNONYM public.DBA_TRIGGERS for compat_tools.DBA_TRIGGERS;
            CREATE OR REPLACE SYNONYM public.ALL_TRIGGERS for compat_tools.ALL_TRIGGERS;
         ELSE
            CREATE OR REPLACE VIEW compat_tools.DBA_TRIGGERS
            AS
            SELECT 
                    CASE
                        WHEN it.trigger_schema::text = lower(it.trigger_schema::text) THEN compat_tools.f_upper_name(it.trigger_schema::text)
                        ELSE it.trigger_schema::text
                    END AS owner, 
                    CASE
                        WHEN it.trigger_name::text = lower(it.trigger_name::text) THEN compat_tools.f_upper_name(it.trigger_name::text)
                        ELSE it.trigger_name::text
                    END AS trigger_name, 
                    compat_tools.f_upper_name((it.action_timing::text || ' '::text) || it.action_orientation::text) AS trigger_type, 
                    compat_tools.f_upper_name( event_manipulation ) as triggering_event,
                    CASE
                        WHEN it.event_object_schema::text = lower(it.event_object_schema::text) THEN compat_tools.f_upper_name(it.event_object_schema::text)
                        ELSE it.event_object_schema::text
                    END AS table_owner, 
                    it.base_object_type, 
                    CASE
                        WHEN it.event_object_table::text = lower(it.event_object_table::text) THEN compat_tools.f_upper_name(it.event_object_table::text)
                        ELSE it.event_object_table::text
                    END AS table_name, 
                    null::text as column_name,
                    'REFERENCING NEW AS NEW OLD AS OLD'::text  AS referencing_names, 
                    it.action_condition AS when_clause, 
                    it.status, 
                    'PL/SQL'::text AS action_type, 
                    it.action_statement AS trigger_body, 
                    it.before_statement, 
                    it.before_row, 
                    it.after_row, 
                    it.after_statement, 
                    it.instead_of_row
            FROM (
            SELECT 
                n.nspname AS trigger_schema, 
                t.tgname AS trigger_name,
                n.nspname AS event_object_schema, 
                c.relname AS event_object_table, 
                    CASE
                        WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN ( SELECT rm.m[1] AS m
                        FROM regexp_matches(pg_get_triggerdef(t.oid), '.{35,} WHEN \((.+)\) EXECUTE PROCEDURE'::text) rm(m)
                        LIMIT 1)
                        ELSE NULL::text
                    END AS action_condition, 
                    ltrim(CONCAT(
                      case  (t.tgtype::INT4 & 4)::BOOL when TRUE then ' OR INSERT' end,
                      case  (t.tgtype::INT4 & 16)::BOOL when TRUE then ' OR UPDATE' end,
                      case  (t.tgtype::INT4 & 8)::BOOL when TRUE then ' OR DELETE' end,
                      case  (t.tgtype::INT4 & 32)::BOOL when TRUE then ' OR TRUNCATE' end
                    ),' OR ') event_manipulation,
                    "substring"(pg_get_triggerdef(t.oid), "position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text) + 47) AS action_statement, 
                    CASE when t.tgtype::integer & 1 <> 1 and  t.tgtype::integer & 66 = 2 THEN 'YES' end ::text before_statement,
                    CASE when t.tgtype::integer & 1 = 1  and  t.tgtype::integer & 66 = 2 THEN 'YES' end ::text before_row,
                    CASE when t.tgtype::integer & 1 = 1  and  t.tgtype::integer & 66 not in (2,64) THEN 'YES' end ::text after_row,
                    CASE when t.tgtype::integer & 1 <> 1 and  t.tgtype::integer & 66 not in (2,64) THEN 'YES' end ::text after_statement,
                    CASE when t.tgtype::integer & 1 = 1  and  t.tgtype::integer & 66 = 64 THEN 'YES' end ::text instead_of_row,
                    CASE t.tgtype & 1 WHEN 1 THEN 'ROW'::text ELSE 'STATEMENT'::text END  AS action_orientation,
                    CASE t.tgtype::integer & 66  WHEN 2 THEN 'BEFORE'::text WHEN 64 THEN 'INSTEAD OF'::text ELSE 'AFTER'::text END AS action_timing,
                    CASE t.tgenabled WHEN 'D'::"char" THEN 'DISABLED'::text ELSE 'ENABLED'::text END AS status,
                    case when C.relkind ='v' then 'VIEW' when C.relkind in ('r','f') then 'TABLE' else 'UNDEFINED' end base_object_type
            FROM pg_namespace n, pg_class c, pg_trigger t
            WHERE n.oid = c.relnamespace AND c.oid = t.tgrelid  
            AND NOT t.tgisinternal AND NOT pg_is_other_temp_schema(n.oid) 
            AND (pg_has_role(c.relowner, 'USAGE'::text) 
            OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) 
            OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES'::text))) it;

            CREATE OR REPLACE VIEW compat_tools.USER_TRIGGERS AS SELECT trigger_name, trigger_type, triggering_event, table_owner, base_object_type, table_name,column_name, referencing_names, when_clause, status, action_type, trigger_body, before_statement, before_row, after_row, after_statement, instead_of_row FROM compat_tools.DBA_TRIGGERS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TRIGGERS for compat_tools.DBA_TRIGGERS;
            CREATE OR REPLACE SYNONYM public.ALL_TRIGGERS for compat_tools.DBA_TRIGGERS;
          end if;
            CREATE OR REPLACE SYNONYM public.USER_TRIGGERS for compat_tools.USER_TRIGGERS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TRIGGERS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TRIGGER_COLS
    -- ALL_TRIGGER_COLS
    -- USER_TRIGGER_COLS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TRIGGER_COLS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TRIGGER_COLS
            AS
            SELECT case when trigger_schema::text = lower(trigger_schema::text) then compat_tools.f_upper_name(trigger_schema::text) else trigger_schema::text end AS TRIGGER_OWNER
                 , case when trigger_name::text = lower(trigger_name::text) then compat_tools.f_upper_name(trigger_name::text) else trigger_name::text end AS TRIGGER_NAME
                 , case when event_object_schema::text = lower(event_object_schema::text) then compat_tools.f_upper_name(event_object_schema::text) else event_object_schema::text end AS TABLE_OWNER
                 , case when event_object_table::text = lower(event_object_table::text) then compat_tools.f_upper_name(event_object_table::text) else event_object_table::text end AS TABLE_NAME
                 , case when event_object_column::text = lower(event_object_column::text) then compat_tools.f_upper_name(event_object_column::text) else event_object_column::text end AS COLUMN_NAME
              FROM information_schema.triggered_update_columns
             WHERE trigger_catalog = current_database();

            CREATE OR REPLACE VIEW compat_tools.USER_TRIGGER_COLS AS SELECT TRIGGER_NAME, TABLE_OWNER, TABLE_NAME, COLUMN_NAME FROM compat_tools.DBA_TRIGGER_COLS WHERE TRIGGER_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TRIGGER_COLS for compat_tools.DBA_TRIGGER_COLS;
            CREATE OR REPLACE SYNONYM public.ALL_TRIGGER_COLS for compat_tools.DBA_TRIGGER_COLS;
            CREATE OR REPLACE SYNONYM public.USER_TRIGGER_COLS for compat_tools.USER_TRIGGER_COLS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TRIGGER_COLS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TYPES
    -- ALL_TYPES
    -- USER_TYPES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TYPES', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TYPES
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when t.typname::text = lower(t.typname::text) then compat_tools.f_upper_name(t.typname::text) else t.typname::text end AS TYPE_NAME
                 , t.OID as TYPE_OID
                 , case t.typtype when 'b' then 'BASE' when 'c' then 'COMPOSITE' when 'd' then 'DOMAIN' when 'e'  then 'ENUM' when 'p' then 'PSEUDO' when 'r' then 'RANGE' else compat_tools.f_upper_name(t.typtype::text) end as TYPECODE
                 , case when t.typisdefined then 'YES' else 'NO' end as PREDEFINED
              FROM pg_catalog.pg_type as t
              JOIN pg_catalog.pg_namespace as n on t.typnamespace = n.oid
             WHERE t.typtype != 'b'
               and t.typname not in (select relname FROM pg_catalog.pg_class WHERE relkind in ('r', 't', 'S', 'v', 'f'));

            CREATE OR REPLACE VIEW compat_tools.USER_TYPES AS SELECT type_name, type_oid, typecode, predefined FROM compat_tools.DBA_TYPES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TYPES for compat_tools.DBA_TYPES;
            CREATE OR REPLACE SYNONYM public.ALL_TYPES for compat_tools.DBA_TYPES;
            CREATE OR REPLACE SYNONYM public.USER_TYPES for compat_tools.USER_TYPES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TYPES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_CONSTRAINTS
    -- ALL_CONSTRAINTS
    -- USER_CONSTRAINTS
    -- =========================================================================
    -- 变更历史：
    --    2022-05-20  1.1  增加 SEARCH_CONDITION 字段，用于记录 Check 约束的表达式
    --    2025-03-05  2.1  增加 R_CONSTRAINT_NAME 字段,修改外键类型为r
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_CONSTRAINTS', '2.1')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_CONSTRAINTS
            AS
            SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
                 , case when cons.conname::text = lower(cons.conname::text) then compat_tools.f_upper_name(cons.conname::text) else cons.conname::text end AS CONSTRAINT_NAME
                 , compat_tools.f_upper_name(case cons.contype when 'f' then 'r' else cons.contype end) as CONSTRAINT_TYPE
                 , case when cls_r.relname::text = lower(cls_r.relname::text) then compat_tools.f_upper_name(cls_r.relname::text) else cls_r.relname::text end AS TABLE_NAME
                 , cons.consrc as SEARCH_CONDITION
                 , case when nsp_f.nspname::text = lower(nsp_f.nspname::text) then compat_tools.f_upper_name(nsp_f.nspname::text) else nsp_f.nspname::text end AS R_OWNER
                 , case when conf.conname::text = lower(conf.conname::text) then compat_tools.f_upper_name(conf.conname::text) else conf.conname::text end as R_CONSTRAINT_NAME
                 , case when cls_f.relname::text = lower(cls_f.relname::text) then compat_tools.f_upper_name(cls_f.relname::text) else cls_f.relname::text end AS R_TABLE_NAME
                 , case cons.confdeltype when 'a' then 'NO ACTION' when 'r' then 'RESTRICT' when 'c' then 'CASCADE' when 'n' then 'SET NULL' when 'd' then 'SET DEFAULT' else compat_tools.f_upper_name(cons.confdeltype::text) end as DELETE_RULE
                 , case when cons.convalidated then 'ENABLED' else 'DISABLED' end as STATUS
                 , case when cons.condeferrable then 'DEFERRABLE' else 'NOT DEFERRABLE' end as DEFERRABLE
                 , case when cons.condeferred then 'DEFERRED' else 'IMMEDIATE' end as DEFERRED
                 , case when cons.convalidated then 'VALIDATED' else 'NOT VALIDATED' end as VALIDATED
                 , case when nsp_i.nspname::text = lower(nsp_i.nspname::text) then compat_tools.f_upper_name(nsp_i.nspname::text) else nsp_i.nspname::text end AS INDEX_OWNER
                 , case when cls_i.relname::text = lower(cls_i.relname::text) then compat_tools.f_upper_name(cls_i.relname::text) else cls_i.relname::text end AS INDEX_NAME
                 , case when cons.convalidated then 'VALIDATED' else 'NOT VALIDATED' end as INVALID
              FROM pg_catalog.pg_constraint cons
              left join pg_catalog.pg_constraint conf on  cons.confrelid = conf.conrelid AND conf.conkey = cons.confkey AND conf.contype in  ('u' ,'p') AND cons.contype = 'f' 
              JOIN pg_catalog.pg_namespace nsp on nsp.oid = cons.connamespace
              LEFT JOIN pg_catalog.pg_class cls_r on cons.conrelid = cls_r.oid
              LEFT JOIN pg_catalog.pg_class cls_f on cons.confrelid = cls_f.oid
              LEFT JOIN pg_catalog.pg_namespace nsp_f on nsp_f.oid = cls_f.relnamespace
              LEFT JOIN pg_catalog.pg_class cls_i on cons.conindid = cls_i.oid
              LEFT JOIN pg_catalog.pg_namespace nsp_i on nsp_i.oid = cls_i.relnamespace
             WHERE nsp.nspname not like 'pg_toast%';

            CREATE OR REPLACE VIEW compat_tools.USER_CONSTRAINTS AS SELECT * FROM compat_tools.DBA_CONSTRAINTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_CONSTRAINTS for compat_tools.DBA_CONSTRAINTS;
            CREATE OR REPLACE SYNONYM public.ALL_CONSTRAINTS for compat_tools.DBA_CONSTRAINTS;
            CREATE OR REPLACE SYNONYM public.USER_CONSTRAINTS for compat_tools.USER_CONSTRAINTS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_CONSTRAINTS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_CONS_COLUMNS
    -- ALL_CONS_COLUMNS
    -- USER_CONS_COLUMNS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_CONS_COLUMNS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_CONS_COLUMNS
            AS
            SELECT case when c.nspname::text = lower(c.nspname::text) then compat_tools.f_upper_name(c.nspname::text) else c.nspname::text end AS OWNER
                 , case when c.conname::text = lower(c.conname::text) then compat_tools.f_upper_name(c.conname::text) else c.conname::text end AS CONSTRAINT_NAME
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
                 , case when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end AS COLUMN_NAME
                 , c.ord as POSITION
              FROM (select ns.nspname
                         , cn.conname
                         , cl.relname
                         , unnest(cn.conkey) as column_Id
                         , case cn.contype when 'c' then NULL else generate_series( 1, array_length(cn.conkey, 1)) end as ord
                         , cn.conrelid as table_oid
                      FROM pg_catalog.pg_constraint as cn
                      join pg_catalog.pg_class as cl on cn.conrelid = cl.oid
                      join pg_catalog.pg_namespace as ns on cn.connamespace = ns.oid
                     WHERE ns.nspname::text not like 'pg_toast%') as c
              JOIN pg_catalog.pg_attribute a on c.table_oid = a.attrelid and c.column_Id = a.attnum;

            CREATE OR REPLACE VIEW compat_tools.USER_CONS_COLUMNS AS SELECT * FROM compat_tools.DBA_CONS_COLUMNS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_CONS_COLUMNS for compat_tools.DBA_CONS_COLUMNS;
            CREATE OR REPLACE SYNONYM public.ALL_CONS_COLUMNS for compat_tools.DBA_CONS_COLUMNS;
            CREATE OR REPLACE SYNONYM public.USER_CONS_COLUMNS for compat_tools.USER_CONS_COLUMNS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_CONS_COLUMNS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_VIEWS
    -- ALL_VIEWS
    -- USER_VIEWS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_VIEWS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_VIEWS
            AS
            SELECT case when schemaname::text = lower(schemaname::text) then compat_tools.f_upper_name(schemaname::text) else schemaname::text end AS OWNER
                 , case when viewname::text = lower(viewname::text) then compat_tools.f_upper_name(viewname::text) else viewname::text end AS VIEW_NAME
                 , length(definition) as TEXT_LENGTH
                 , definition as TEXT
                 , definition as TEXT_VC
              FROM pg_catalog.pg_views;

            CREATE OR REPLACE VIEW compat_tools.USER_VIEWS AS SELECT VIEW_NAME, TEXT_LENGTH, "text", TEXT_VC FROM compat_tools.DBA_VIEWS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_VIEWS for compat_tools.DBA_VIEWS;
            CREATE OR REPLACE SYNONYM public.ALL_VIEWS for compat_tools.DBA_VIEWS;
            CREATE OR REPLACE SYNONYM public.USER_VIEWS for compat_tools.USER_VIEWS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_VIEWS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TABLES
    -- DBA_ALL_TABLES
    -- ALL_TABLES
    -- ALL_ALL_TABLES
    -- USER_TABLES
    -- USER_ALL_TABLES
    -- TAB
    -- =========================================================================
    -- 变更历史：
    --   2022-01-26  [1.0 -> 1.1]    增加段管理字段 SEGMENT_MANAGED
    --   2022-05-30  [1.1 -> 1.2]    增加同义词 ALL_ALL_TABLES, USER_ALL_TABLES
    --   2023-10-09  [2.0 -> 2.1]    修改全局临时表的临时表属性为YES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TABLES', '2.1')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TABLES
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
                 , compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
                 , case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
                 , c.reltuples::bigint as NUM_ROWS
                 , c.relpages::bigint as BLOCKS
                 , s.stawidth as AVG_ROW_LEN
                 , coalesce(pg_stat_get_last_analyze_time(c.oid), pg_stat_get_last_autoanalyze_time(c.oid)) as LAST_ANALYZED
                 , case c.parttype when 'n' then 'NO' else 'YES' end as PARTITIONED
                 , case c.relpersistence when 't' then 'YES' when 'g' then 'YES' else 'NO' end as TEMPORARY
                 , case when c.relrowmovement then 'ENABLE' else 'DISABLE' end as ROW_MOVEMENT
                 , case when arraycontains(reloptions, string_to_array('compression=no', ',')) then 'NO' else 'YES' end as COMPRESSION
                 , case when arraycontains(reloptions, string_to_array('compression=no', ',')) then NULL
                        when arraycontains(reloptions, string_to_array('compresslevel=3', ',')) then 3
                        when arraycontains(reloptions, string_to_array('compresslevel=2', ',')) then 2
                        when arraycontains(reloptions, string_to_array('compresslevel=1', ',')) then 1
                        ELSE 0
                   end as COMPRESSION_LEVEL
                 , case when arraycontains(reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
                 , case when arraycontains(reloptions, string_to_array('orientation=column', ',')) then 'COLUMN' else 'ROW' end as ORIENTATION
                 , case when arraycontains(reloptions, string_to_array('storage_type=ustore', ',')) then 'INPLACE' else 'APPEND' end as STORAGE_TYPE
              FROM pg_catalog.pg_class c
              join pg_catalog.pg_namespace n on c.relnamespace = n.oid
              left join pg_catalog.pg_tablespace t on c.reltablespace = t.oid
              left join (select starelid, sum(stawidth) as stawidth FROM pg_catalog.pg_statistic group by starelid) s on c.oid = s.starelid
             WHERE c.relkind in ('r', 'f')
               and n.nspname::text not like 'pg_toast%';

            CREATE OR REPLACE VIEW compat_tools.USER_TABLES AS SELECT table_name, tablespace_name, logging, num_rows, blocks, avg_row_len, last_analyzed, partitioned, temporary, row_movement, compression, compression_level, segment_managed, orientation, storage_type FROM compat_tools.DBA_TABLES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TABLES for compat_tools.DBA_TABLES;
            CREATE OR REPLACE SYNONYM public.ALL_TABLES for compat_tools.DBA_TABLES;
            CREATE OR REPLACE SYNONYM public.USER_TABLES for compat_tools.USER_TABLES;
            CREATE OR REPLACE SYNONYM public.DBA_ALL_TABLES for compat_tools.DBA_TABLES;
            CREATE OR REPLACE SYNONYM public.ALL_ALL_TABLES for compat_tools.DBA_TABLES;
            CREATE OR REPLACE SYNONYM public.USER_ALL_TABLES for compat_tools.USER_TABLES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TABLES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- TAB
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'TAB', '2.0')
        then
            create view compat_tools.tab
            as
            with nsp_oid as (select oid from pg_namespace where nspname = current_schema())
            select relname as TNAME
                 , case relkind when 'v' then 'VIEW' else 'TABLE' end as TABTYPE
                 , null as CLUSTERID
              from pg_class
             where relkind in ('t','r','f','v')
               and relnamespace in (select oid from nsp_oid)
             union
            select synname
                 , 'SYNONYM'
                 , null as CLUSTERID
              from pg_synonym
             where synnamespace in (select oid from nsp_oid);

            create or replace synonym public.tab for compat_tools.tab;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in TAB: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_PART_TABLES
    -- ALL_PART_TABLES
    -- USER_PART_TABLES
    -- =========================================================================
    -- 2022-01-26  [1.0 -> 2.0]    增加子分区相关字段，增加段管理字段 SEGMENT_MANAGED
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_PART_TABLES', '3.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_PART_TABLES
            AS
            SELECT OWNER
                 , TABLE_NAME
                 , max(PARTITIONING_TYPE) as PARTITIONING_TYPE
                 , max(SUBPARTITIONING_TYPE) as SUBPARTITIONING_TYPE
                 , count(distinct part_oid) as PARTITION_COUNT
                 , count(distinct sub_oid) as SUBPARTITION_COUNT
                 , max(PARTITIONING_KEY_COUNT) as PARTITIONING_KEY_COUNT
                 , max(SUBPARTITIONING_KEY_COUNT) as SUBPARTITIONING_KEY_COUNT
                 , max(DEF_TABLESPACE_NAME) as DEF_TABLESPACE_NAME
                 , MAX(DEF_LOGGING) as DEF_LOGGING
                 , max(INTERVAL) as INTERVAL
                 , max(SEGMENT_MANAGED) as SEGMENT_MANAGED
              from (SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                         , case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end AS TABLE_NAME
                         , case p.partstrategy
                           when 'r' then 'RANGE'
                           when 'v' then 'VALUE'
                           when 'i' then 'INTERVAL'
                           when 'l' then 'LIST'
                           when 'h' then 'HASH'
                           when 'n' then 'INVALID'
                           else compat_tools.f_upper_name(p.partstrategy::text)
                           end as PARTITIONING_TYPE
                         , case when pp.partkey is null
                                then null
                                else case pp.partstrategy
                                     when 'r' then 'RANGE'
                                     when 'v' then 'VALUE'
                                     when 'i' then 'INTERVAL'
                                     when 'l' then 'LIST'
                                     when 'h' then 'HASH'
                                     when 'n' then 'INVALID'
                                     else compat_tools.f_upper_name(pp.partstrategy::text)
                                end
                           end as SUBPARTITIONING_TYPE
                         -- , count(p.parentid) over (partition by p.parentid) - 1 as PARTITION_COUNT
                         , pp.oid as part_oid
                         , ppp.oid as sub_oid
                         , array_length(p.partkey::int[], 1) as PARTITIONING_KEY_COUNT
                         , array_length(pp.partkey::int[], 1) as SUBPARTITIONING_KEY_COUNT
                         , compat_tools.f_upper_name(coalesce(s.spcname::text, 'default')) as DEF_TABLESPACE_NAME
                         , case c.relpersistence when 'p' then 'YES' else 'NO' end as DEF_LOGGING
                         , case when arraycontains(p.reloptions, string_to_array('compression=no',',')) then 'NO' else 'YES' end as DEF_COMPRESSION
                         , case when arraycontains(p.reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
                         , p.interval
                         , p.parttype
                      FROM pg_catalog.pg_partition p   -- 父表
                      join pg_catalog.pg_class c on p.parentid = c.oid  -- 父表详细信息
                      join pg_catalog.pg_namespace n on c.relnamespace = n.oid  -- 父表 schema
                      join pg_catalog.pg_partition pp on p.parentid = pp.parentid and pp.parttype = 'p'  -- 分区
                      left join pg_catalog.pg_partition ppp on pp.oid = ppp.parentid and ppp.parttype = 's'  -- 子分区
                      left join pg_catalog.pg_tablespace s on p.reltablespace = s.oid  -- 父表表空间
                     where p.parttype = 'r'
                   ) as p
             group by owner, table_name;

            CREATE OR REPLACE VIEW compat_tools.USER_PART_TABLES AS SELECT TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, SUBPARTITION_COUNT, PARTITIONING_KEY_COUNT, SUBPARTITIONING_KEY_COUNT, DEF_TABLESPACE_NAME, DEF_LOGGING, INTERVAL, SEGMENT_MANAGED FROM compat_tools.DBA_PART_TABLES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_PART_TABLES for compat_tools.DBA_PART_TABLES;
            CREATE OR REPLACE SYNONYM public.ALL_PART_TABLES for compat_tools.DBA_PART_TABLES;
            CREATE OR REPLACE SYNONYM public.USER_PART_TABLES for compat_tools.USER_PART_TABLES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_PART_TABLES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TAB_PARTITIONS
    -- ALL_TAB_PARTITIONS
    -- USER_TAB_PARTITIONS
    -- =========================================================================
    -- 2022-01-26  [1.0 -> 1.1]    增加段管理字段 SEGMENT_MANAGED，移除 AVG_ROW_LEN (只能记录到父表，不能精确到分区)
    -- 2024-03-29  [2.1]           增加 interval 字段
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_PARTITIONS', '2.1')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_PARTITIONS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS TABLE_OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
                 , case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end AS PARTITION_NAME
                 , coalesce(array_to_string(p.boundaries, ','), 'MAXVALUE') as HIGH_VALUE
                 , length(array_to_string(p.boundaries, ',')) as HIGH_VALUE_LENGTH
                 , row_number() over (partition by p.parentid order by p.boundaries) as PARTITION_POSITION
                 , compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
                 , case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
                 , case when arraycontains(p.reloptions, string_to_array('compression=no',',')) then 'NO' else 'YES' end as COMPRESSION
                 , case when arraycontains(p.reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
                 , p.reltuples::bigint as NUM_ROWS
                 , p.relpages::bigint as BLOCKS
                 , coalesce(pg_stat_get_last_analyze_time(p.parentid), pg_stat_get_last_autoanalyze_time(p.parentid)) as LAST_ANALYZED
                 , case when pp.partstrategy = 'i' then 'YES' else 'NO' end as interval
              FROM pg_catalog.pg_partition p
              join pg_catalog.pg_class c on p.parentid = c.oid
              join pg_catalog.pg_partition pp on p.parentid = pp.parentid and pp.parttype = 'r'
              join pg_catalog.pg_namespace n on c.relnamespace = n.oid
              left join pg_catalog.pg_tablespace t on p.reltablespace = t.oid
             WHERE p.parttype = 'p';

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_PARTITIONS AS SELECT table_name, partition_name, high_value, high_value_length, partition_position, tablespace_name, logging, compression, segment_managed, num_rows, blocks, last_analyzed, interval FROM compat_tools.DBA_TAB_PARTITIONS WHERE TABLE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TAB_PARTITIONS for compat_tools.DBA_TAB_PARTITIONS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_PARTITIONS for compat_tools.DBA_TAB_PARTITIONS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_PARTITIONS for compat_tools.USER_TAB_PARTITIONS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_PARTITIONS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TAB_SUBPARTITIONS
    -- ALL_TAB_SUBPARTITIONS
    -- USER_TAB_SUBPARTITIONS
    -- =========================================================================
    -- 2022-01-26  [1.0]    新增视图
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_SUBPARTITIONS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_SUBPARTITIONS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end as TABLE_OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end as TABLE_NAME
                 , case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end as PARTITION_NAME
                 , case when sp.relname::text = lower(sp.relname::text) then compat_tools.f_upper_name(sp.relname::text) else sp.relname::text end as SUBPARTITION_NAME
                 , coalesce(array_to_string(sp.boundaries, ','), 'MAXVALUE') as HIGH_VALUE
                 , length(array_to_string(sp.boundaries, ',')) as HIGH_VALUE_LENGTH
                 , dense_rank() over (partition by p.parentid order by p.boundaries) as PARTITION_POSITION
                 , row_number() over (partition by sp.parentid order by sp.boundaries) as SUBPARTITION_POSITION
                 , compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
                 , case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
                 , case when arraycontains(sp.reloptions, string_to_array('compression=no',',')) then 'NO' else 'YES' end as COMPRESSION
                 , case when arraycontains(sp.reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
                 , sp.reltuples::bigint as NUM_ROWS
                 , sp.relpages::bigint as BLOCKS
                 , coalesce(pg_stat_get_last_analyze_time(p.parentid), pg_stat_get_last_autoanalyze_time(p.parentid)) as LAST_ANALYZED
                 , sp.INTERVAL
                 , case when sp.indisusable then 'YES' else 'NO' end as INDEXING
              FROM pg_catalog.pg_partition p  -- 分区
              join pg_catalog.pg_partition sp on p.oid = sp.parentid and sp.parttype = 's'  -- 子分区
              join pg_catalog.pg_class c on p.parentid = c.oid  -- 父表详细信息
              join pg_catalog.pg_namespace n on c.relnamespace = n.oid  -- 父表 schema
              left join pg_catalog.pg_tablespace t on p.reltablespace = t.oid  -- 父表表空间
             WHERE p.parttype = 'p';

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_SUBPARTITIONS AS SELECT table_name, partition_name, subpartition_name, high_value, high_value_length, partition_position, subpartition_position, tablespace_name, logging, compression, segment_managed, num_rows, blocks, last_analyzed, interval, indexing FROM compat_tools.DBA_TAB_SUBPARTITIONS WHERE TABLE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TAB_SUBPARTITIONS for compat_tools.DBA_TAB_SUBPARTITIONS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_SUBPARTITIONS for compat_tools.DBA_TAB_SUBPARTITIONS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_SUBPARTITIONS for compat_tools.USER_TAB_SUBPARTITIONS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_SUBPARTITIONS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_PART_KEY_COLUMNS
    -- ALL_PART_KEY_COLUMNS
    -- USER_PART_KEY_COLUMNS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_PART_KEY_COLUMNS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_PART_KEY_COLUMNS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end as OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end as NAME
                 , CASE c.relkind
                        WHEN 'r'::"char" THEN 'TABLE'::text
                        WHEN 'i'::"char" THEN 'INDEX'::text
                        ELSE relkind::text
                   END AS OBJECT_TYPE
                 , case when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end as COLUMN_NAME
                 , p.ord as COLUMN_POSITION
              FROM (select parentid
                         , unnest(partkey) as partkey
                         , generate_series( 1, array_length(partkey, 1)) as ord
                      from pg_catalog.pg_partition
                     where parttype = 'r') p  -- 分区表主表
              join pg_catalog.pg_class c on p.parentid = c.oid  -- 父表详细信息
              join pg_catalog.pg_namespace n on c.relnamespace = n.oid  -- 父表 schema
              join pg_catalog.pg_attribute a on p.parentid = a.attrelid and p.partkey = a.attnum;  -- 分区键;

            CREATE OR REPLACE VIEW compat_tools.USER_PART_KEY_COLUMNS AS SELECT NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION FROM compat_tools.DBA_PART_KEY_COLUMNS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_PART_KEY_COLUMNS for compat_tools.DBA_PART_KEY_COLUMNS;
            CREATE OR REPLACE SYNONYM public.ALL_PART_KEY_COLUMNS for compat_tools.DBA_PART_KEY_COLUMNS;
            CREATE OR REPLACE SYNONYM public.USER_PART_KEY_COLUMNS for compat_tools.USER_PART_KEY_COLUMNS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_PART_KEY_COLUMNS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_SUBPART_KEY_COLUMNS
    -- ALL_SUBPART_KEY_COLUMNS
    -- USER_SUBPART_KEY_COLUMNS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_SUBPART_KEY_COLUMNS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_SUBPART_KEY_COLUMNS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end as OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end as NAME
                 , CASE c.relkind
                        WHEN 'r'::"char" THEN 'TABLE'::text
                        WHEN 'i'::"char" THEN 'INDEX'::text
                        ELSE relkind::text
                   END AS OBJECT_TYPE
                 , case when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end as COLUMN_NAME
                 , p.ord as COLUMN_POSITION
              FROM (select distinct parentid
                         , unnest(partkey) as partkey
                         , generate_subscripts(partkey, 1) + 1 as ord
                      from pg_catalog.pg_partition
                     where parttype = 'p'
                       and array_length(partkey, 1) > 0) p  -- 子分区表的分区： 类型为 p 且 partkey 不为空
              join pg_catalog.pg_class c on p.parentid = c.oid  -- 父表详细信息
              join pg_catalog.pg_namespace n on c.relnamespace = n.oid  -- 父表 schema
              join pg_catalog.pg_attribute a on p.parentid = a.attrelid and p.partkey = a.attnum;  -- 分区键;

            CREATE OR REPLACE VIEW compat_tools.USER_SUBPART_KEY_COLUMNS AS SELECT NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION FROM compat_tools.DBA_SUBPART_KEY_COLUMNS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_SUBPART_KEY_COLUMNS for compat_tools.DBA_SUBPART_KEY_COLUMNS;
            CREATE OR REPLACE SYNONYM public.ALL_SUBPART_KEY_COLUMNS for compat_tools.DBA_SUBPART_KEY_COLUMNS;
            CREATE OR REPLACE SYNONYM public.USER_SUBPART_KEY_COLUMNS for compat_tools.USER_SUBPART_KEY_COLUMNS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_SUBPART_KEY_COLUMNS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TAB_STATISTICS
    -- ALL_TAB_STATISTICS
    -- USER_TAB_STATISTICS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_STATISTICS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_STATISTICS
            AS
            SELECT OWNER
                 , TABLE_NAME
                 , Null as PARTITION_NAME
                 , null as PARTITION_POSITION
                 , 'TABLE' as OBJECT_TYPE
                 , NUM_ROWS
                 , BLOCKS
                 , AVG_ROW_LEN
                 , LAST_ANALYZED
              FROM compat_tools.dba_tables
             WHERE partitioned = 'NO'
             union all
            SELECT TABLE_OWNER as OWNER
                 , TABLE_NAME
                 , PARTITION_NAME
                 , PARTITION_POSITION
                 , 'PARTITION' as OBJECT_TYPE
                 , NUM_ROWS
                 , BLOCKS
                 , null as AVG_ROW_LEN
                 , LAST_ANALYZED
              FROM compat_tools.DBA_TAB_PARTITIONS;

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_STATISTICS AS SELECT table_name, partition_name, partition_position, object_type, num_rows, blocks, avg_row_len, last_analyzed FROM compat_tools.DBA_TAB_STATISTICS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TAB_STATISTICS for compat_tools.DBA_TAB_STATISTICS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_STATISTICS for compat_tools.DBA_TAB_STATISTICS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_STATISTICS for compat_tools.USER_TAB_STATISTICS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_STATISTICS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TAB_COMMENTS
    -- ALL_TAB_COMMENTS
    -- USER_TAB_COMMENTS
    -- =========================================================================
    --   2022-05-26  1.1  增加没有注释的表的展示，缩减 pg_class 对象类型
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_COMMENTS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COMMENTS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
                 , CASE c.relkind
                        WHEN 'r'::"char" THEN 'TABLE'::text
                        WHEN 'v'::"char" THEN 'VIEW'::text
                        WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
                        ELSE c.relkind::text
                    END AS TABLE_TYPE
                 , d.description as COMMENTS
              FROM pg_catalog.pg_class c
              JOIN pg_catalog.pg_namespace n on c.relnamespace  = n.oid
              LEFT JOIN pg_catalog.pg_description d on c.oid = d.objoid and d.objsubid = 0 and d.classoid = 'pg_class'::regclass::oid
             WHERE n.nspname not like 'pg_toast%'
               AND c.relkind in ('r', 'v', 'f');

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_COMMENTS AS SELECT table_name, table_type, comments FROM compat_tools.DBA_TAB_COMMENTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TAB_COMMENTS for compat_tools.DBA_TAB_COMMENTS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_COMMENTS for compat_tools.DBA_TAB_COMMENTS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_COMMENTS for compat_tools.USER_TAB_COMMENTS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_COMMENTS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_COL_COMMENTS
    -- ALL_COL_COMMENTS
    -- USER_COL_COMMENTS
    -- =========================================================================
    --   20250121  2.1  屏蔽隐藏列
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_COL_COMMENTS', '2.1')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_COL_COMMENTS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
                 , case when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end AS COLUMN_NAME
                 , d.description as COMMENTS
              FROM pg_catalog.pg_attribute a
              JOIN pg_catalog.pg_class c on a.attrelid = c.oid
              JOIN pg_catalog.pg_namespace n on c.relnamespace  = n.oid
              LEFT JOIN pg_catalog.pg_description d on a.attrelid = d.objoid and a.attnum = d.objsubid and d.classoid = 'pg_class'::regclass::oid
             WHERE n.nspname not like 'pg_toast%' 
                AND a.attnum > 0
                AND NOT a.attisdropped;

            CREATE OR REPLACE VIEW compat_tools.USER_COL_COMMENTS AS SELECT table_name, column_name, comments FROM compat_tools.DBA_COL_COMMENTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_COL_COMMENTS for compat_tools.DBA_COL_COMMENTS;
            CREATE OR REPLACE SYNONYM public.ALL_COL_COMMENTS for compat_tools.DBA_COL_COMMENTS;
            CREATE OR REPLACE SYNONYM public.USER_COL_COMMENTS for compat_tools.USER_COL_COMMENTS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_COL_COMMENTS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TAB_MODIFICATIONS
    -- ALL_TAB_MODIFICATIONS
    -- USER_TAB_MODIFICATIONS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_MODIFICATIONS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TAB_MODIFICATIONS
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS TABLE_OWNER
                 , case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end AS TABLE_NAME
                 , s.n_tup_ins as INSERTS
                 , s.n_tup_upd as UPDATES
                 , s.n_tup_del as DELETES
                 , coalesce(s.last_analyze, s.last_autovacuum) as TIMESTAMP
              FROM pg_catalog.pg_class p
              join pg_catalog.pg_namespace n on p.relnamespace = n.oid
              join pg_catalog.pg_stat_all_tables s on p.oid = s.relid
             WHERE p.relkind = 'r';

            CREATE OR REPLACE VIEW compat_tools.USER_TAB_MODIFICATIONS AS SELECT TABLE_NAME, INSERTS, UPDATES, DELETES, "timestamp" FROM compat_tools.DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_TAB_MODIFICATIONS for compat_tools.DBA_TAB_MODIFICATIONS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_MODIFICATIONS for compat_tools.DBA_TAB_MODIFICATIONS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_MODIFICATIONS for compat_tools.USER_TAB_MODIFICATIONS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_MODIFICATIONS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_INDEXES
    -- ALL_INDEXES
    -- USER_INDEXES
    -- IND
    -- =========================================================================
    -- 2025-02-06  [2.0 -> 2.1]   增加INDEX_TYPE字段
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_INDEXES', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_INDEXES
            AS
            SELECT case when nc.nspname::text = lower(nc.nspname::text) then compat_tools.f_upper_name(nc.nspname::text) else nc.nspname::text end AS OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS INDEX_NAME
                 , case /*when INDISCLUSTERED then 'CLUSTER'::text*/
                    when 0=all(indoption) and INDEXPRS is NULL then 'NORMAL'::text 
                    else  'FUNCTION-BASED NORMAL'::text END INDEX_TYPE
                 , case when nt.nspname::text = lower(nt.nspname::text) then compat_tools.f_upper_name(nt.nspname::text) else nt.nspname::text end AS TABLE_OWNER
                 , case when t.relname::text = lower(t.relname::text) then compat_tools.f_upper_name(t.relname::text) else t.relname::text end AS TABLE_NAME
                 , CASE t.relkind
                        WHEN 'r'::"char" THEN 'TABLE'::text
                        WHEN 'v'::"char" THEN 'VIEW'::text
                        WHEN 'i'::"char" THEN 'INDEX'::text
                        WHEN 'I'::"char" THEN 'PARTITIONED INDEX'::text
                        WHEN 'S'::"char" THEN 'SEQUENCE'::text
                        WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
                        WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
                        WHEN 't'::"char" THEN 'TOAST'::text
                        ELSE t.relkind::text
                    END AS TABLE_TYPE
                 , case when i.indisunique then 'UNIQUE' else 'NONUNIQUE' end as UNIQUENESS
                 , compat_tools.f_upper_name(coalesce(s.spcname::text, 'default')) as TABLESPACE_NAME
                 , case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
                 , CASE WHEN st.stadistinct >= 0 THEN st.stadistinct ELSE ROUND(ABS(st.stadistinct * c.RELTUPLES)) END as DISTINCT_KEYS
                 , case when i.indisvalid then 'VALID' else 'UNUSABLE' end as STATUS
                 , c.reltuples as NUM_ROWS
                 , coalesce(pg_stat_get_last_analyze_time(t.oid), pg_stat_get_last_autoanalyze_time(t.oid)) as LAST_ANALYZED
                 , case when c.relkind = 'I' then 'YES' else 'NO' end as PARTITIONED
                 , case c.relpersistence when 't' then 'YES' else 'NO' end as TEMPORARY
              FROM pg_catalog.pg_index i
              join pg_catalog.pg_class c on i.indexrelid = c.oid
              join pg_catalog.pg_class t on i.indrelid = t.oid
              join pg_catalog.pg_namespace nc on nc.oid = c.relnamespace
              join pg_catalog.pg_namespace nt on nt.oid = t.relnamespace
              left join pg_catalog.pg_tablespace s on c.reltablespace = s.oid
              left join pg_catalog.pg_statistic st on i.indnatts = 1 and i.indkey[0] = st.staattnum and st.starelid = t.oid
             WHERE nc.nspname::text not like 'pg_toast%';

            CREATE OR REPLACE VIEW compat_tools.USER_INDEXES AS SELECT index_name, INDEX_TYPE, table_owner, table_name, table_type, uniqueness, tablespace_name, logging, distinct_keys, status, num_rows, last_analyzed, partitioned, temporary FROM compat_tools.DBA_INDEXES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_INDEXES for compat_tools.DBA_INDEXES;
            CREATE OR REPLACE SYNONYM public.ALL_INDEXES for compat_tools.DBA_INDEXES;
            CREATE OR REPLACE SYNONYM public.USER_INDEXES for compat_tools.USER_INDEXES;
            CREATE OR REPLACE SYNONYM public.IND for compat_tools.USER_INDEXES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_INDEXES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_INDEX_USAGE
    -- ALL_INDEX_USAGE
    -- USER_INDEX_USAGE
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_INDEX_USAGE', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_INDEX_USAGE
            AS
            SELECT i.oid as object_id
                 , case when i.relname::text = lower(i.relname::text) then compat_tools.f_upper_name(i.relname::text) else i.relname::text end AS NAME
                 , case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , pg_stat_get_numscans(i.oid) as TOTAL_ACCESS_COUNT
                 , pg_stat_get_tuples_returned(i.oid) as TOTAL_ROWS_RETURNED
              FROM pg_catalog.pg_class i
              join pg_catalog.pg_namespace n on n.oid = i.relnamespace
             WHERE i.relkind in ('i') and n.nspname::text not like 'pg_toast%';

            CREATE OR REPLACE VIEW compat_tools.USER_INDEX_USAGE AS SELECT object_id, name, total_access_count, total_rows_returned FROM compat_tools.DBA_INDEX_USAGE WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_INDEX_USAGE for compat_tools.DBA_INDEX_USAGE;
            CREATE OR REPLACE SYNONYM public.ALL_INDEX_USAGE for compat_tools.DBA_INDEX_USAGE;
            CREATE OR REPLACE SYNONYM public.USER_INDEX_USAGE for compat_tools.USER_INDEX_USAGE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_INDEX_USAGE: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_IND_COLUMNS
    -- ALL_IND_COLUMNS
    -- USER_IND_COLUMNS
    -- =========================================================================
    -- 变更历史：
    --    2022-05-20  1.1  支持函数索引字段的展示，字段名称统一展示为 SYS_FUNCTION_EXPR，额外添加 COLUMN_EXPRESSION 可避免与 dba_ind_expressions 视图关联
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_IND_COLUMNS', '2.0')
        then
            -- 创建必要的函数，用于解析函数索引的字段表达式
            create or replace function pg_get_expr_array (expr_list text)
            returns text[] immutable strict as $$
            declare
                l_result      text[]   := '{}';
                l_len_left    int      := 0;
                l_len_right   int      := 0;
                l_temp_elem   text     := '';
                l_rec         record;
            begin
                for l_rec in select t
                                  , length(replace(regexp_replace(t, '''[^'']+''', ''), '(', '')) as len_left
                                  , length(replace(regexp_replace(t, '''[^'']+''', ''), ')', '')) as len_right
                               from regexp_split_to_table(expr_list, ',') as t
                loop
                    l_len_left := l_len_left + l_rec.len_left;
                    l_len_right := l_len_right + l_rec.len_right;
                    l_temp_elem := l_temp_elem || l_rec.t;
                    if l_len_left = l_len_right
                    then
                        l_result := l_result || trim(l_temp_elem);
                        l_len_left := 0;
                        l_len_right := 0;
                        l_temp_elem := '';
                    end if;
                end loop;
                if l_temp_elem != '' and l_temp_elem is not null
                then
                    l_result := l_result || trim(l_temp_elem);
                end if;
                return l_result;
            end;
            $$ language plpgsql;

            -- 创建视图
            CREATE OR REPLACE VIEW compat_tools.DBA_IND_COLUMNS
            AS
            SELECT case when nc.nspname::text = lower(nc.nspname::text) then compat_tools.f_upper_name(nc.nspname::text) else nc.nspname::text end AS INDEX_OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS INDEX_NAME
                 , case when nt.nspname::text = lower(nt.nspname::text) then compat_tools.f_upper_name(nt.nspname::text) else nt.nspname::text end AS TABLE_OWNER
                 , case when t.relname::text = lower(t.relname::text) then compat_tools.f_upper_name(t.relname::text) else t.relname::text end AS TABLE_NAME
                 , case when a.attname is null then 'SYS_FUNCTION_EXPR' when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end AS COLUMN_NAME
                 , case when i.indkey = 0 then indexprs[row_number() over (partition by i.indexrelid, i.indkey order by ord)] else null end AS COLUMN_EXPRESSION
                 , i.ord as COLUMN_POSITION
                 , a.attlen as COLUMN_LENGTH
                 , case i.indoption when 0 then 'ASC' when 3 then 'DESC' end as DESCEND
              FROM (select indexrelid, indrelid, unnest(indkey) as indkey, unnest(indoption) as indoption, pg_get_expr_array(pg_get_expr(indexprs, indrelid, 'true')) as indexprs
                         , generate_series( 1, array_length(indkey, 1)) as ord
                      FROM pg_catalog.pg_index) i
              join pg_catalog.pg_class c on i.indexrelid = c.oid
              join pg_catalog.pg_class t on i.indrelid = t.oid
              join pg_catalog.pg_namespace nc on nc.oid = c.relnamespace
              join pg_catalog.pg_namespace nt on nt.oid = t.relnamespace
              left join pg_catalog.pg_attribute a on a.attrelid = i.indrelid and i.indkey = a.attnum
             WHERE nc.nspname::text not like 'pg_toast%';

            CREATE OR REPLACE VIEW compat_tools.USER_IND_COLUMNS AS SELECT index_name, table_owner, table_name, column_name, column_position, column_length, descend FROM compat_tools.DBA_IND_COLUMNS WHERE INDEX_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_IND_COLUMNS for compat_tools.DBA_IND_COLUMNS;
            CREATE OR REPLACE SYNONYM public.ALL_IND_COLUMNS for compat_tools.DBA_IND_COLUMNS;
            CREATE OR REPLACE SYNONYM public.USER_IND_COLUMNS for compat_tools.USER_IND_COLUMNS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_IND_COLUMNS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_IND_EXPRESSIONS
    -- ALL_IND_EXPRESSIONS
    -- USER_IND_EXPRESSIONS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_IND_EXPRESSIONS', '2.0')
        then
            -- 创建必要的函数，用于解析函数索引的字段表达式
            create or replace function pg_get_expr_array (expr_list text)
            returns text[] immutable strict as $$
            declare
                l_result      text[]   := '{}';
                l_len_left    int      := 0;
                l_len_right   int      := 0;
                l_temp_elem   text     := '';
                l_rec         record;
            begin
                for l_rec in select t
                                  , length(replace(regexp_replace(t, '''[^'']+''', ''), '(', '')) as len_left
                                  , length(replace(regexp_replace(t, '''[^'']+''', ''), ')', '')) as len_right
                               from regexp_split_to_table(expr_list, ',') as t
                loop
                    l_len_left := l_len_left + l_rec.len_left;
                    l_len_right := l_len_right + l_rec.len_right;
                    l_temp_elem := l_temp_elem || l_rec.t;
                    if l_len_left = l_len_right
                    then
                        l_result := l_result || trim(l_temp_elem);
                        l_len_left := 0;
                        l_len_right := 0;
                        l_temp_elem := '';
                    end if;
                end loop;
                if l_temp_elem != '' and l_temp_elem is not null
                then
                    l_result := l_result || trim(l_temp_elem);
                end if;
                return l_result;
            end;
            $$ language plpgsql;

            -- 创建视图
            CREATE OR REPLACE VIEW compat_tools.DBA_IND_EXPRESSIONS
            AS
            SELECT case when nc.nspname::text = lower(nc.nspname::text) then compat_tools.f_upper_name(nc.nspname::text) else nc.nspname::text end AS INDEX_OWNER
                 , case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS INDEX_NAME
                 , case when nt.nspname::text = lower(nt.nspname::text) then compat_tools.f_upper_name(nt.nspname::text) else nt.nspname::text end AS TABLE_OWNER
                 , case when t.relname::text = lower(t.relname::text) then compat_tools.f_upper_name(t.relname::text) else t.relname::text end AS TABLE_NAME
                 , indexprs[row_number() over (partition by indexrelid order by ord)] AS COLUMN_EXPRESSION
                 , i.ord as COLUMN_POSITION
              FROM (select indexrelid, indrelid, unnest(indkey) as indkey, unnest(indoption) as indoption, pg_get_expr_array(pg_get_expr(indexprs, indrelid, 'true')) as indexprs
                         , generate_series( 1, array_length(indkey, 1)) as ord
                      FROM pg_catalog.pg_index) i
              join pg_catalog.pg_class c on i.indexrelid = c.oid
              join pg_catalog.pg_class t on i.indrelid = t.oid
              join pg_catalog.pg_namespace nc on nc.oid = c.relnamespace
              join pg_catalog.pg_namespace nt on nt.oid = t.relnamespace
             WHERE nc.nspname::text not like 'pg_toast%'
               AND i.indkey = 0;  -- 只显示函数表达式字段

            CREATE OR REPLACE VIEW compat_tools.USER_IND_EXPRESSIONS AS SELECT index_name, table_owner, table_name, column_expression, column_position FROM compat_tools.DBA_IND_EXPRESSIONS WHERE INDEX_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_IND_EXPRESSIONS for compat_tools.DBA_IND_EXPRESSIONS;
            CREATE OR REPLACE SYNONYM public.ALL_IND_EXPRESSIONS for compat_tools.DBA_IND_EXPRESSIONS;
            CREATE OR REPLACE SYNONYM public.USER_IND_EXPRESSIONS for compat_tools.USER_IND_EXPRESSIONS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_IND_EXPRESSIONS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_IND_PARTITIONS
    -- ALL_IND_PARTITIONS
    -- USER_IND_PARTITIONS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_IND_PARTITIONS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_IND_PARTITIONS
            AS
            SELECT case when ni.nspname::text = lower(ni.nspname::text) then compat_tools.f_upper_name(ni.nspname::text) else ni.nspname::text end AS INDEX_OWNER
                 , case when ci.relname::text = lower(ci.relname::text) then compat_tools.f_upper_name(ci.relname::text) else ci.relname::text end AS INDEX_NAME
                 , case when pi.relname::text = lower(pi.relname::text) then compat_tools.f_upper_name(pi.relname::text) else pi.relname::text end AS PARTITION_NAME
                 , case when nt.nspname::text = lower(nt.nspname::text) then compat_tools.f_upper_name(nt.nspname::text) else nt.nspname::text end AS TABLE_OWNER
                 , case when ct.relname::text = lower(ct.relname::text) then compat_tools.f_upper_name(ct.relname::text) else ct.relname::text end AS TABLE_NAME
                 , coalesce(array_to_string(pt.boundaries, ','), 'MAXVALUE') as HIGH_VALUE
                 , length(array_to_string(pt.boundaries, ',')) as HIGH_VALUE_LENGTH
                 , row_number() over (partition by pt.parentid order by pt.boundaries) as PARTITION_POSITION
                 , compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
                 , case ct.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
                 , pi.reltuples as NUM_ROWS
                 , coalesce(pg_stat_get_last_analyze_time(ct.oid), pg_stat_get_last_autoanalyze_time(ct.oid)) as LAST_ANALYZED
              FROM pg_catalog.pg_partition pi
              join pg_catalog.pg_partition pt on pi.indextblid = pt.oid
              join pg_catalog.pg_class ci on pi.parentid = ci.oid
              join pg_catalog.pg_class ct on pt.parentid = ct.oid
              join pg_catalog.pg_namespace ni on ci.relnamespace = ni.oid
              join pg_catalog.pg_namespace nt on ct.relnamespace = nt.oid
              left join pg_catalog.pg_tablespace t on pi.reltablespace = t.oid
             WHERE pi.parttype = 'x';

            CREATE OR REPLACE VIEW compat_tools.USER_IND_PARTITIONS AS SELECT index_name, partition_name, table_owner, table_name, high_value, high_value_length, partition_position, tablespace_name, logging, num_rows, last_analyzed FROM compat_tools.DBA_IND_PARTITIONS WHERE INDEX_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_IND_PARTITIONS for compat_tools.DBA_IND_PARTITIONS;
            CREATE OR REPLACE SYNONYM public.ALL_IND_PARTITIONS for compat_tools.DBA_IND_PARTITIONS;
            CREATE OR REPLACE SYNONYM public.USER_IND_PARTITIONS for compat_tools.USER_IND_PARTITIONS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_IND_PARTITIONS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_IND_STATISTICS
    -- ALL_IND_STATISTICS
    -- USER_IND_STATISTICS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_IND_STATISTICS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_IND_STATISTICS
            AS
            SELECT OWNER
                 , INDEX_NAME
                 , TABLE_OWNER
                 , TABLE_NAME
                 , null as PARTITION_NAME
                 , null as PARTITION_POSITION
                 , 'INDEX' as OBJECT_TYPE
                 , DISTINCT_KEYS
                 , NUM_ROWS
                 , LAST_ANALYZED
              FROM compat_tools.DBA_INDEXES
             WHERE partitioned = 'NO'
             union all
            select INDEX_OWNER as OWNER
                 , INDEX_NAME
                 , TABLE_OWNER
                 , TABLE_NAME
                 , PARTITION_NAME
                 , PARTITION_POSITION
                 , 'PARTITION' as OBJECT_TYPE
                 , null as DISTINCT_KEYS
                 , NUM_ROWS
                 , LAST_ANALYZED
              FROM compat_tools.DBA_IND_PARTITIONS;

            CREATE OR REPLACE VIEW compat_tools.USER_IND_STATISTICS AS SELECT * FROM compat_tools.DBA_IND_STATISTICS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_IND_STATISTICS for compat_tools.DBA_IND_STATISTICS;
            CREATE OR REPLACE SYNONYM public.ALL_IND_STATISTICS for compat_tools.DBA_IND_STATISTICS;
            CREATE OR REPLACE SYNONYM public.USER_IND_STATISTICS for compat_tools.USER_IND_STATISTICS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_IND_STATISTICS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_PART_INDEXES
    -- ALL_PART_INDEXES
    -- USER_PART_INDEXES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_PART_INDEXES', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_PART_INDEXES
            AS
            SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
                 , case when i.relname::text = lower(i.relname::text) then compat_tools.f_upper_name(i.relname::text) else i.relname::text end AS INDEX_NAME
                 , case when tp.relname::text = lower(tp.relname::text) then compat_tools.f_upper_name(tp.relname::text) else tp.relname::text end AS TABLE_NAME
                 , case tp.partstrategy
                   when 'r' then 'RANGE'
                   when 'v' then 'VALUE'
                   when 'i' then 'INTERVAL'
                   when 'l' then 'LIST'
                   when 'h' then 'HASH'
                   when 'n' then 'INVALID'
                   else compat_tools.f_upper_name(tp.partstrategy::text) end as PARTITIONING_TYPE
                 , coalesce(ip.part_count, 0) as PARTITION_COUNT
                 , array_length(tp.partkey::int[], 1) as PARTITIONING_KEY_COUNT
                 , compat_tools.f_upper_name(coalesce(s.spcname::text, 'default')) as DEF_TABLESPACE_NAME
                 , case i.relpersistence when 'p' then 'YES' else 'NO' end as DEF_LOGGING
              FROM pg_catalog.pg_index it
              join pg_catalog.pg_class i on i.oid = it.indexrelid
              join pg_catalog.pg_namespace n on i.relnamespace = n.oid
              join pg_catalog.pg_partition tp on tp.parentid = it.indrelid and tp.parttype = 'r'
              left join (select parentid, count(*) part_count FROM pg_catalog.pg_partition group by parentid) ip on ip.parentid = it.indexrelid
              left join pg_catalog.pg_tablespace s on i.reltablespace = s.oid
             WHERE n.nspname not like 'pg_toast%';

            CREATE OR REPLACE VIEW compat_tools.USER_PART_INDEXES AS SELECT index_name, table_name, partitioning_type, partition_count, partitioning_key_count, def_tablespace_name, def_logging FROM compat_tools.DBA_PART_INDEXES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_PART_INDEXES for compat_tools.DBA_PART_INDEXES;
            CREATE OR REPLACE SYNONYM public.ALL_PART_INDEXES for compat_tools.DBA_PART_INDEXES;
            CREATE OR REPLACE SYNONYM public.USER_PART_INDEXES for compat_tools.USER_PART_INDEXES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_PART_INDEXES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_USERS
    -- ALL_USERS
    -- USER_USERS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_USERS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_USERS
            AS
            SELECT case when usename::text = lower(usename::text) then compat_tools.f_upper_name(usename::text) else usename::text end AS USERNAME
                 , usesysid as USER_ID
                 , passwd as PASSWORD
                 , CASE s.rolstatus
                   WHEN 2 THEN 'LOCK (ADMIN)'
                   WHEN 1 THEN 'LOCK (FAILURE)'
                   ELSE 'NORMAL'
                   END as account_status
                 , CASE WHEN s.rolstatus in (1, 2) then locktime else null END as LOCK_DATE
                 , valuntil as EXPIRY_DATE
                 , compat_tools.f_upper_name(respool::text) as PROFILE
              FROM pg_catalog.pg_user u
              LEFT JOIN pg_catalog.pg_user_status s on u.usesysid = s.roloid;

            CREATE OR REPLACE VIEW compat_tools.USER_USERS AS SELECT USERNAME, USER_ID, account_status, LOCK_DATE, EXPIRY_DATE FROM compat_tools.DBA_USERS WHERE USERNAME = (case when current_user::text = lower(current_user::text) then compat_tools.f_upper_name(current_user::text) else current_user::text end);

            CREATE OR REPLACE SYNONYM public.DBA_USERS for compat_tools.DBA_USERS;
            CREATE OR REPLACE SYNONYM public.ALL_USERS for compat_tools.DBA_USERS;
            CREATE OR REPLACE SYNONYM public.USER_USERS for compat_tools.USER_USERS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_USERS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_ROLES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_ROLES', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_ROLES
            AS
            SELECT case when rolname::text = lower(rolname::text) then compat_tools.f_upper_name(rolname::text) else rolname::text end AS ROLE
                 , 'NO' as PASSWORD_REQUIRED
                 , 'NONE' as AUTHENTICATION_TYPE
                 , 'YES' as COMMON
              FROM pg_catalog.pg_roles;

            CREATE OR REPLACE SYNONYM public.DBA_ROLES for compat_tools.DBA_ROLES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_ROLES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_ROLE_PRIVS
    -- USER_ROLE_PRIVS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_ROLE_PRIVS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_ROLE_PRIVS
            AS
            SELECT case when grantee.rolname::text = lower(grantee.rolname::text) then compat_tools.f_upper_name(grantee.rolname::text) else grantee.rolname::text end AS GRANTEE
                 , case when granted.rolname::text = lower(granted.rolname::text) then compat_tools.f_upper_name(granted.rolname::text) else granted.rolname::text end AS GRANTED_ROLE
                 , case when m.admin_option then 'YES' else 'NO' end as ADMIN_OPTION
                 , CASE WHEN grantee.rolinherit THEN 'YES' ELSE 'NO' END AS default_role
                 , 'NO' as DELEGATE_OPTION
                 , 'YES' as COMMON
              FROM pg_catalog.pg_auth_members m
              JOIN pg_catalog.pg_roles as grantee on m.member = grantee.oid
              JOIN pg_catalog.pg_roles as granted on m.roleid = granted.oid;

            CREATE OR REPLACE VIEW compat_tools.USER_ROLE_PRIVS AS SELECT GRANTEE as USERNAME, GRANTED_ROLE, ADMIN_OPTION, default_role, DELEGATE_OPTION, COMMON FROM compat_tools.DBA_ROLE_PRIVS WHERE GRANTEE = (case when current_user::text = lower(current_user::text) then compat_tools.f_upper_name(current_user::text) else current_user::text end);

            CREATE OR REPLACE SYNONYM public.DBA_ROLE_PRIVS for compat_tools.DBA_ROLE_PRIVS;
            CREATE OR REPLACE SYNONYM public.USER_ROLE_PRIVS for compat_tools.USER_ROLE_PRIVS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_ROLE_PRIVS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- PRODUCT_COMPONENT_VERSION
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'PRODUCT_COMPONENT_VERSION', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.PRODUCT_COMPONENT_VERSION
            AS
              WITH t_ver as (select row_number() over () as id, ver_item from regexp_split_to_table(version(), '\s+') as ver_item)
            SELECT case when n.name_item = '' or n.name_item is null then 'openGauss' else n.name_item end || ' Enterprise Edition' AS product
                 , v.ver_item AS VERSION
                 , version() AS VERSION_FULL
                 , case when version() ~ 'x86_64' then '64bit Production' else 'Production' end AS status
              FROM (select ver_item from t_ver where id = (select id - 1 from t_ver where ver_item = 'build')
                     union all
                    select ver_item from t_ver where not exists (select 1 from t_ver where ver_item = 'build') and id = 2) as v
                 , (select trim(ver_item, '() ') as name_item from t_ver where id = (select id - 2 from t_ver where ver_item = 'build')
                     union all
                    select trim(ver_item, '() ') as name_item from t_ver where not exists (select 1 from t_ver where ver_item = 'build') and id = 1) as n
             UNION ALL
            SELECT extname AS product
                 , extversion AS VERSION
                 , NULL as VERSION_FULL
                 , 'Production' AS status
              FROM pg_extension;


            CREATE OR REPLACE SYNONYM public.PRODUCT_COMPONENT_VERSION for compat_tools.PRODUCT_COMPONENT_VERSION;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in PRODUCT_COMPONENT_VERSION: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- PLAN_TABLE
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'PLAN_TABLE', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.PLAN_TABLE
            AS
            SELECT statement_id
                 , plan_id
                 , id
                 , operation
                 , options
                 , object_name
                 , object_type
                 , object_owner
                 , projection
              FROM plan_table_data
             WHERE session_id = pg_current_sessionid()
               AND user_id = pg_current_userid();

            CREATE OR REPLACE SYNONYM public.PLAN_TABLE for compat_tools.PLAN_TABLE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in PLAN_TABLE: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$PARAMETER
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$PARAMETER', '1.1')
        then
            CREATE OR REPLACE VIEW compat_tools.V$PARAMETER
            AS
            SELECT NAME
                 , case vartype when 'bool' then 1 when 'string' then 2 when 'integer' then 3 when 'enum' then 4 when 'real' then 6 end as TYPE
                 , vartype as TYPE_NAME
                 , setting as VALUE
                 , setting as DISPLAY_VALUE
                 , boot_val as DEFAULT_VALUE
                 , case when setting = boot_val then 'TRUE' else 'FALSE' end as ISDEFAULT
                 , case context when 'internal' then 'FLASE' else 'TRUE' end as ISMODIFIED
                 , case context when 'internal' then 'TRUE' else 'FALSE' end as ISADJUSTED
                 , short_desc as DESCRIPTION
              FROM pg_catalog.pg_settings;

            create view compat_tools.GV$PARAMETER as select 1 as inst_id, t.* from compat_tools.v$PARAMETER as t;

            CREATE OR REPLACE SYNONYM public.V$PARAMETER for compat_tools.V$PARAMETER;
            CREATE OR REPLACE SYNONYM public.GV$PARAMETER for compat_tools.GV$PARAMETER;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$PARAMETER: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$SPPARAMETER
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$SPPARAMETER', '1.1')
        then
            CREATE OR REPLACE VIEW compat_tools.V$SPPARAMETER
            AS
            SELECT NAME
                 , case vartype when 'bool' then 1 when 'string' then 2 when 'integer' then 3 when 'enum' then 4 when 'real' then 6 end as TYPE
                 , vartype as TYPE_NAME
                 , setting as VALUE
                 , setting as DISPLAY_VALUE
                 , case when sourcefile is null then 'FALSE' else 'TRUE' end as ISSPECIFIED
              FROM pg_catalog.pg_settings;

            create view compat_tools.GV$SPPARAMETER as select 1 as inst_id, t.* from compat_tools.v$SPPARAMETER as t;

            CREATE OR REPLACE SYNONYM public.V$SPPARAMETER for compat_tools.V$SPPARAMETER;
            CREATE OR REPLACE SYNONYM public.GV$SPPARAMETER for compat_tools.GV$SPPARAMETER;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$SPPARAMETER: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$VERSION
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$VERSION', '1.1')
        then
            CREATE OR REPLACE VIEW compat_tools.V$VERSION
            AS
            select regexp_substr(version(), 'MogDB[^\)]+') as banner
             union all
            select regexp_substr(version(), 'compiled at[\s\d\-\:]+')
             union all
            select replace(regexp_substr(version(), 'on\s+[^,]+'), 'on', 'Platform architecture:');

            create view compat_tools.GV$VERSION as select 1 as inst_id, t.* from compat_tools.v$VERSION as t;

            CREATE OR REPLACE SYNONYM public.V$VERSION for compat_tools.V$VERSION;
            CREATE OR REPLACE SYNONYM public.GV$VERSION for compat_tools.GV$VERSION;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$VERSION: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$PARAMETER_VALID_VALUES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$PARAMETER_VALID_VALUES', '1.1')
        then
            CREATE OR REPLACE VIEW compat_tools.V$PARAMETER_VALID_VALUES
            AS
            SELECT NAME
                 , row_number() over (partition by name) as ORDINAL
                 , VALUE
                 , case when boot_val = VALUE then 'TRUE' else 'FALSE' end as ISDEFAULT
              FROM (select name, boot_val, unnest(enumvals) as VALUE FROM pg_catalog.pg_settings) as p;

            create view compat_tools.GV$PARAMETER_VALID_VALUES as select 1 as inst_id, t.* from compat_tools.v$PARAMETER_VALID_VALUES as t;

            CREATE OR REPLACE SYNONYM public.V$PARAMETER_VALID_VALUES for compat_tools.V$PARAMETER_VALID_VALUES;
            CREATE OR REPLACE SYNONYM public.GV$PARAMETER_VALID_VALUES for compat_tools.GV$PARAMETER_VALID_VALUES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$PARAMETER_VALID_VALUES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$SESSION
    -- =========================================================================
    -- 变更历史：
    --   1.2 : 增加字段 SQL_EXEC_START/BLOCKING_SESSION_STATUS/WAIT_TIME_MICRO
    --   1.3 : 增加字段 serial#/audsid/user#
    --   1.4 : 调整audsid取值方式
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$SESSION', '1.3')
        then
            CREATE OR REPLACE VIEW compat_tools.V$SESSION
            AS
            SELECT a.sessionid as SID
                 , 0::int as serial#
                 , case when trim(connection_info) is null then 0 
                     else (select lwpid from pg_stat_get_thread() gt where gt.pid=a.pid) 
                   end as audsid
                 , a.usesysid as user#
                 , a.usename as USERNAME
                 , a.state as STATUS
                 , coalesce(a.client_hostname, a.client_addr::text) as MACHINE
                 , a.client_port as PORT
                 , a.application_name as PROGRAM
                 , case when trim(connection_info) is null then 'BACKGROUND' else 'USER' end as TYPE
                 , a.query_id as  SQL_ID
                 , a.query as SQL_TEXT
                 , a.query_start as SQL_EXEC_START
                 , a.connection_info as CLIENT_INFO
                 , a.backend_start as LOGON_TIME
                 , case when a.waiting then 'VALID' else 'NOT IN WAIT' end as BLOCKING_SESSION_STATUS
                 , w.wait_status as WAIT_CLASS
                 , w.wait_event as EVENT
                 , 'Thread' as P1TEXT
                 , w.tid as P1
                 , 'LW Thread' as P2TEXT
                 , w.lwtid as P2
                 , 'Thread Level' as P3TEXT
                 , w.tlevel as P3
                 , extract(epoch from now() - a.state_change) * 1000000 as WAIT_TIME_MICRO
              FROM pg_catalog.pg_stat_activity as a
              LEFT JOIN (select * from pg_stat_get_status(NULL::bigint)) as w on a.sessionid = w.sessionid;

            create view compat_tools.GV$SESSION as select 1 as inst_id, t.* from compat_tools.v$SESSION as t;

            CREATE OR REPLACE SYNONYM public.V$SESSION for compat_tools.V$SESSION;
            CREATE OR REPLACE SYNONYM public.GV$SESSION for compat_tools.GV$SESSION;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$SESSION: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$SYSTEM_WAIT_CLASS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$SYSTEM_WAIT_CLASS', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$SYSTEM_WAIT_CLASS
            AS
            SELECT TYPE as WAIT_CLASS
                 , SUM(wait) as TOTAL_WAITS
                 , sum(total_wait_time)/10000 as TIME_WAITED
                 , sum(avg_wait_time)/10000 as AVG_TIME_WAITED
                 , max(avg_wait_time)/10000 as MAX_TIME_WAITED
                 , min(avg_wait_time)/10000 as MIN_TIME_WAITED
                 , max(last_updated) as last_updated
              FROM get_instr_wait_event(NULL::integer) get_instr_wait_event(nodename, type, event, wait, failed_wait, total_wait_time, avg_wait_time, max_wait_time, min_wait_time, last_updated)
             group by TYPE;

            create view compat_tools.GV$SYSTEM_WAIT_CLASS as select 1 as inst_id, t.* from compat_tools.V$SYSTEM_WAIT_CLASS as t;

            CREATE OR REPLACE SYNONYM public.V$SYSTEM_WAIT_CLASS for compat_tools.V$SYSTEM_WAIT_CLASS;
            CREATE OR REPLACE SYNONYM public.GV$SYSTEM_WAIT_CLASS for compat_tools.GV$SYSTEM_WAIT_CLASS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$SYSTEM_WAIT_CLASS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- FUNCTION: F_QUERY_STATMENT()
    -- 用于处理 v$sql 视图在普通用户下的访问权限
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('FUNCTION', 'F_QUERY_STATMENT()', '1.0')
        then
            create function compat_tools.f_query_statment()
            returns table( node_name name
                         , node_id integer
                         , user_name name
                         , user_id oid
                         , unique_sql_id bigint
                         , query text
                         , n_calls bigint
                         , min_elapse_time bigint
                         , max_elapse_time bigint
                         , total_elapse_time bigint
                         , n_returned_rows bigint
                         , n_tuples_fetched bigint
                         , n_tuples_returned bigint
                         , n_tuples_inserted bigint
                         , n_tuples_updated bigint
                         , n_tuples_deleted bigint
                         , n_blocks_fetched bigint
                         , n_blocks_hit bigint
                         , n_soft_parse bigint
                         , n_hard_parse bigint
                         , db_time bigint
                         , cpu_time bigint
                         , execution_time bigint
                         , parse_time bigint
                         , plan_time bigint
                         , rewrite_time bigint
                         , pl_execution_time bigint
                         , pl_compilation_time bigint
                         , data_io_time bigint
                         , net_send_info text
                         , net_recv_info text
                         , net_stream_send_info text
                         , net_stream_recv_info text
                         , last_updated timestamp with time zone
                         , sort_count bigint
                         , sort_time bigint
                         , sort_mem_used bigint
                         , sort_spill_count bigint
                         , sort_spill_size bigint
                         , hash_count bigint
                         , hash_time bigint
                         , hash_mem_used bigint
                         , hash_spill_count bigint
                         , hash_spill_size bigint)
            SECURITY DEFINER
            as $$
            SELECT node_name
                 , node_id
                 , user_name
                 , user_id
                 , unique_sql_id
                 , query
                 , n_calls
                 , min_elapse_time
                 , max_elapse_time
                 , total_elapse_time
                 , n_returned_rows
                 , n_tuples_fetched
                 , n_tuples_returned
                 , n_tuples_inserted
                 , n_tuples_updated
                 , n_tuples_deleted
                 , n_blocks_fetched
                 , n_blocks_hit
                 , n_soft_parse
                 , n_hard_parse
                 , db_time
                 , cpu_time
                 , execution_time
                 , parse_time
                 , plan_time
                 , rewrite_time
                 , pl_execution_time
                 , pl_compilation_time
                 , data_io_time
                 , net_send_info
                 , net_recv_info
                 , net_stream_send_info
                 , net_stream_recv_info
                 , last_updated
                 , sort_count
                 , sort_time
                 , sort_mem_used
                 , sort_spill_count
                 , sort_spill_size
                 , hash_count
                 , hash_time
                 , hash_mem_used
                 , hash_spill_count
                 , hash_spill_size
              FROM get_instr_unique_sql() get_instr_unique_sql( node_name
                                                              , node_id
                                                              , user_name
                                                              , user_id
                                                              , unique_sql_id
                                                              , query
                                                              , n_calls
                                                              , min_elapse_time
                                                              , max_elapse_time
                                                              , total_elapse_time
                                                              , n_returned_rows
                                                              , n_tuples_fetched
                                                              , n_tuples_returned
                                                              , n_tuples_inserted
                                                              , n_tuples_updated
                                                              , n_tuples_deleted
                                                              , n_blocks_fetched
                                                              , n_blocks_hit
                                                              , n_soft_parse
                                                              , n_hard_parse
                                                              , db_time
                                                              , cpu_time
                                                              , execution_time
                                                              , parse_time
                                                              , plan_time
                                                              , rewrite_time
                                                              , pl_execution_time
                                                              , pl_compilation_time
                                                              , data_io_time
                                                              , net_send_info
                                                              , net_recv_info
                                                              , net_stream_send_info
                                                              , net_stream_recv_info
                                                              , last_updated
                                                              , sort_count
                                                              , sort_time
                                                              , sort_mem_used
                                                              , sort_spill_count
                                                              , sort_spill_size
                                                              , hash_count
                                                              , hash_time
                                                              , hash_mem_used
                                                              , hash_spill_count
                                                              , hash_spill_size);
            $$ language sql;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in F_QUERY_STATMENT(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$SQL
    -- 依赖于 F_QUERY_STATMENT() 函数
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$SQL', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$SQL
            AS
            select substr(query, 1, 1000) as SQL_TEXT
                 , query as SQL_FULLTEXT
                 , unique_sql_id as SQL_ID
                 , sort_count as SORTS
                 , n_calls as EXECUTIONS
                 , n_soft_parse + n_hard_parse as PARSE_CALLS
                 , n_blocks_fetched - n_blocks_hit as DISK_READS
                 , n_blocks_fetched as BUFFER_GETS
                 , pl_execution_time as PLSQL_EXEC_TIME
                 , n_returned_rows as ROWS_PROCESSED
                 , user_id as PARSING_USER_ID
                 , user_name as PARSING_SCHEMA_NAME
                 , hashtext(query) as HASH_VALUE
                 , cpu_time as CPU_TIME
                 , total_elapse_time as ELAPSED_TIME
                 , unique_sql_id as FORCE_MATCHING_SIGNATURE
                 , last_updated as LAST_ACTIVE_TIME
                 , db_time
                 , unique_sql_id
                 , n_calls
                 , min_elapse_time
                 , max_elapse_time
                 , total_elapse_time
                 , n_returned_rows
                 , n_tuples_fetched
                 , n_tuples_returned
                 , n_tuples_inserted
                 , n_tuples_updated
                 , n_tuples_deleted
                 , n_blocks_fetched
                 , n_blocks_hit
                 , n_soft_parse
                 , n_hard_parse
                 , execution_time
                 , parse_time
                 , plan_time
                 , rewrite_time
                 , pl_execution_time
                 , pl_compilation_time
                 , data_io_time
                 , net_send_info
                 , net_recv_info
                 , net_stream_send_info
                 , net_stream_recv_info
                 , last_updated
                 , sort_count
                 , sort_time
                 , sort_mem_used
                 , sort_spill_count
                 , sort_spill_size
                 , hash_count
                 , hash_time
                 , hash_mem_used
                 , hash_spill_count
                 , hash_spill_size
              from compat_tools.f_query_statment();

            create view compat_tools.GV$SQL as select 1 as inst_id, t.* from compat_tools.V$SQL as t;

            CREATE OR REPLACE SYNONYM public.V$SQL for compat_tools.V$SQL;
            CREATE OR REPLACE SYNONYM public.GV$SQL for compat_tools.GV$SQL;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$SQL: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$OPTION
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$OPTION', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$OPTION
            AS
            select name as parameter
                 , case when installed_version is not null then 'TRUE' else 'FALSE' end as VALUE
              from pg_available_extensions;

            create view compat_tools.GV$OPTION as select 1 as inst_id, t.* from compat_tools.V$OPTION as t;

            CREATE OR REPLACE SYNONYM public.V$OPTION for compat_tools.V$OPTION;
            CREATE OR REPLACE SYNONYM public.GV$OPTION for compat_tools.GV$OPTION;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$OPTION: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$LICENSE
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$LICENSE', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$LICENSE
            AS
            select current_setting('max_connections') as SESSIONS_MAX
                 , current_setting('connection_alarm_rate') * current_setting('max_connections') as SESSIONS_WARNING
                 , (select count(*) from pg_stat_activity) as SESSIONS_CURRENT
                 , current_setting('max_connections') as SESSIONS_HIGHWATER
                 , 0 as USERS_MAX
                 , (select value from GS_OS_RUN_INFO where name = 'NUM_CPUS') as CPU_COUNT_CURRENT
                 , (select value from GS_OS_RUN_INFO where name = 'NUM_CPU_CORES') as CPU_CORE_COUNT_CURRENT
                 , (select value from GS_OS_RUN_INFO where name = 'NUM_CPU_SOCKETS') as CPU_SOCKET_COUNT_CURRENT
                 , (select value from GS_OS_RUN_INFO where name = 'NUM_CPUS') as CPU_COUNT_HIGHWATER
                 , (select value from GS_OS_RUN_INFO where name = 'NUM_CPU_CORES') as CPU_CORE_COUNT_HIGHWATER
                 , (select value from GS_OS_RUN_INFO where name = 'NUM_CPU_SOCKETS') as CPU_SOCKET_COUNT_HIGHWATER;

            create view compat_tools.GV$LICENSE as select 1 as inst_id, t.* from compat_tools.V$LICENSE as t;

            CREATE OR REPLACE SYNONYM public.V$LICENSE for compat_tools.V$LICENSE;
            CREATE OR REPLACE SYNONYM public.GV$LICENSE for compat_tools.GV$LICENSE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$LICENSE: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_DETAIL_PRIVILEGES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_DETAIL_PRIVILEGES', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_DETAIL_PRIVILEGES
            AS
            SELECT /*+no nestloop*/ t.oid, t.oid_class, t.type, t.privilege_type
                 , r.rolname as owner, n.nspname as schema
                 , t.name, t.column_name
                 , ro.rolname as grantor, re.rolname as grantee
                 , t.privilege, t.is_grantable
              FROM (select oid, 'pg_default_acl' as oid_class
                         , case defaclobjtype when 'r' then 'RELATION' when 'S' then 'SEQUENCE' when 'f' then 'FUNCTION' when 'T' then 'TYPE' when 'n' then 'SCHEMA' else defaclobjtype::text end as type
                         , 'DEFAULT PRIVILEGES' as privilege_type
                         , defaclrole as owner
                         , defaclnamespace as schema
                         , null as NAME
                         , NULL as column_name
                         , (aclexplode(defaclacl)).grantor AS grantor
                         , (aclexplode(defaclacl)).grantee AS grantee
                         , (aclexplode(defaclacl)).privilege_type AS privilege
                         , (aclexplode(defaclacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_default_acl
                     where defaclacl is not null
                     union all
                    select oid, 'pg_class' as oid_class
                         , case relkind when 'r' then 'TABLE' when 'S' then 'SEQUENCE' when 'v' then 'VIEW' when 'm' then 'MATERIALIZED VIEW' when 'f' then 'FOREIGN TABLE' when 'p' then 'PARTITIONED TABLE' else relkind::text end as type
                         , case relkind when 'S' then 'SEQUENCE' else 'TABLE' end as privilege_type
                         , relowner as owner
                         , relnamespace as schema
                         , relname::text as NAME
                         , NULL as column_name
                         , (aclexplode(relacl)).grantor AS grantor
                         , (aclexplode(relacl)).grantee AS grantee
                         , (aclexplode(relacl)).privilege_type AS privilege
                         , (aclexplode(relacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_class
                     where relacl is not null
                     union all
                    select c.oid, 'pg_class' as oid_class
                         , 'COLUMN' as type
                         , 'TABLE (COLUMN)' as privilege_type
                         , c.relowner as owner
                         , c.relnamespace as schema
                         , c.relname::text as name
                         , a.attname::text as column_name
                         , (aclexplode(a.attacl)).grantor AS grantor
                         , (aclexplode(a.attacl)).grantee AS grantee
                         , (aclexplode(a.attacl)).privilege_type AS privilege
                         , (aclexplode(a.attacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_attribute as a
                      join pg_catalog.pg_class as c on a.attrelid = c.oid
                     where a.attacl is not null
                     union all
                    select oid, 'pg_database' as oid_class
                         , 'DATABASE' as type
                         , 'DATABASE' as privilege_type
                         , datdba as owner
                         , null as schema
                         , datname::text as name
                         , null as column_name
                         , (aclexplode(datacl)).grantor AS grantor
                         , (aclexplode(datacl)).grantee AS grantee
                         , (aclexplode(datacl)).privilege_type AS privilege
                         , (aclexplode(datacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_database
                     where datacl is not null
                     union all
                    select oid, 'pg_foreign_data_wrapper' as oid_class
                         , 'FOREIGN DATA WRAPPER' as type
                         , 'FOREIGN DATA WRAPPER' as privilege_type
                         , fdwowner as owner
                         , null as schema
                         , fdwname::text as name
                         , null as column_name
                         , (aclexplode(fdwacl)).grantor AS grantor
                         , (aclexplode(fdwacl)).grantee AS grantee
                         , (aclexplode(fdwacl)).privilege_type AS privilege
                         , (aclexplode(fdwacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_foreign_data_wrapper
                     where fdwacl is not null
                     union all
                    select oid, 'pg_foreign_server' as oid_class
                         , 'FOREIGN SERVER' as type
                         , 'FOREIGN SERVER' as privilege_type
                         , srvowner as owner
                         , null as schema
                         , srvname::text as name
                         , null as column_name
                         , (aclexplode(srvacl)).grantor AS grantor
                         , (aclexplode(srvacl)).grantee AS grantee
                         , (aclexplode(srvacl)).privilege_type AS privilege
                         , (aclexplode(srvacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_foreign_server
                     where srvacl is not null
                     union all
                    select oid, 'pg_type' as oid_class
                         , case typtype when 'b' then 'BASE' when 'c' then 'COMPOSITE' when 'd' then 'DOMAIN' when 'e'  then 'ENUM' when 'p' then 'PSEUDO' when 'r' then 'RANGE' else typtype::text end as type
                         , case typtype when 'd' then 'DOMAIN' else 'TYPE' end as privilege_type
                         , typowner as owner
                         , typnamespace as schema
                         , typname::text as name
                         , null as column_name
                         , (aclexplode(typacl)).grantor AS grantor
                         , (aclexplode(typacl)).grantee AS grantee
                         , (aclexplode(typacl)).privilege_type AS privilege
                         , (aclexplode(typacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_type
                     where typacl is not null
                     union all
                    select oid, 'pg_proc' as oid_class
                         , case prokind when 'f' then 'FUNCTION' when 'p' then 'PROCEDURE' when 'a' then 'AGGREGATE' when 'w' then 'WINDOW' else prokind::text end as type
                         , case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end as privilege_type
                         , proowner as owner
                         , pronamespace as schema
                         , proname::text as name
                         , null as column_name
                         , (aclexplode(proacl)).grantor AS grantor
                         , (aclexplode(proacl)).grantee AS grantee
                         , (aclexplode(proacl)).privilege_type AS privilege
                         , (aclexplode(proacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_proc
                     where proacl is not null
                     union all
                    select oid, 'pg_language' as oid_class
                         , 'LANGUAGE' as type
                         , 'LANGUAGE' as privilege_type
                         , lanowner as owner
                         , null as schema
                         , lanname::text as name
                         , null as column_name
                         , (aclexplode(lanacl)).grantor AS grantor
                         , (aclexplode(lanacl)).grantee AS grantee
                         , (aclexplode(lanacl)).privilege_type AS privilege
                         , (aclexplode(lanacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_language
                     where lanacl is not null
                     union all
                    select oid, 'pg_largeobject_metadata' as oid_class
                         , 'LARGE OBJECT' as type
                         , 'LARGE OBJECT' as privilege_type
                         , lomowner as owner
                         , null as schema
                         , oid::text as name
                         , null as column_name
                         , (aclexplode(lomacl)).grantor AS grantor
                         , (aclexplode(lomacl)).grantee AS grantee
                         , (aclexplode(lomacl)).privilege_type AS privilege
                         , (aclexplode(lomacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_largeobject_metadata
                     where lomacl is not null
                     union all
                    select oid, 'pg_namespace' as oid_class
                         , 'SCHEMA' as type
                         , 'SCHEMA' as privilege_type
                         , nspowner as owner
                         , null as schema
                         , nspname::text as name
                         , null as column_name
                         , (aclexplode(nspacl)).grantor AS grantor
                         , (aclexplode(nspacl)).grantee AS grantee
                         , (aclexplode(nspacl)).privilege_type AS privilege
                         , (aclexplode(nspacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_namespace
                     where nspacl is not null
                     union all
                    select oid, 'pg_tablespace' as oid_class
                         , 'TABLESPACE' as type
                         , 'TABLESPACE' as privilege_type
                         , spcowner as owner
                         , null as schema
                         , spcname::text as name
                         , null as column_name
                         , (aclexplode(spcacl)).grantor AS grantor
                         , (aclexplode(spcacl)).grantee AS grantee
                         , (aclexplode(spcacl)).privilege_type AS privilege
                         , (aclexplode(spcacl)).is_grantable AS is_grantable
                      from pg_catalog.pg_tablespace
                     where spcacl is not null
                   ) t
              join pg_catalog.pg_roles r on t.owner = r.oid
              join pg_catalog.pg_namespace n on t.schema = n.oid
              left join pg_catalog.pg_roles ro on t.grantor = ro.oid
              left join pg_catalog.pg_roles re on t.grantee = re.oid
             where t.grantor != t.grantee
               and t.grantee != 0
               and t.grantor != 0;

            CREATE OR REPLACE SYNONYM public.DBA_DETAIL_PRIVILEGES for compat_tools.DBA_DETAIL_PRIVILEGES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_DETAIL_PRIVILEGES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_ALL_PRIVILEGES_SQL
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_ALL_PRIVILEGES_SQL', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_ALL_PRIVILEGES_SQL
            AS
            with all_privs as (select oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, is_grantable
                                 from compat_tools.dba_detail_privileges
                                group by oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, is_grantable
                               having (privilege_type = 'TABLE' and count(*) = 7)
                                   or (privilege_type = 'TABLE (COLUMN)' and count(*) = 4)
                                   or (privilege_type = 'SEQUENCE' and count(*) = 3)
                                   or (privilege_type = 'DATABASE' and count(*) = 3)
                                   or (privilege_type = 'LARGE OBJECT' and count(*) = 2)
                                   or (privilege_type = 'SCHEMA' and count(*) = 2)
                                   or (privilege_type = 'DEFAULT PRIVILEGES' and type = 'RELATION' and count(*) = 7)
                                   or (privilege_type = 'DEFAULT PRIVILEGES' and type = 'SEQUENCE' and count(*) = 3)
                                   or (privilege_type = 'DEFAULT PRIVILEGES' and type = 'SCHEMA' and count(*) = 2)
                              )
            SELECT t.*
                 , case when privilege_type = 'TABLE (COLUMN)'
                        then 'GRANT '||privilege||' ('||column_name||') on TABLE '||schema||'.'||name||' to '||grantee
                        when privilege_type = 'DEFAULT PRIVILEGES'
                        then 'alter default privileges for user '||grantor||' GRANT '||privilege||' on '||(case type when 'RELATION' then 'TABLE' else type end)||'S to '||grantee
                        else 'GRANT '||privilege||' on '||privilege_type||' '||(case when schema is not null then schema||'.'||name else name end)||' to '||grantee
                   end as grant_sql
                 , case when privilege_type = 'TABLE (COLUMN)'
                        then 'REVOKE '||privilege||' ('||column_name||') on TABLE '||schema||'.'||name||' from '||grantee
                        when privilege_type = 'DEFAULT PRIVILEGES'
                        then 'alter default privileges for user '||grantor||' REVOKE '||privilege||' on '||(case type when 'RELATION' then 'TABLE' else type end)||'S from '||grantee
                        else 'REVOKE '||privilege||' on '||privilege_type||' '||(case when schema is not null then schema||'.'||name else name end)||' from '||grantee
                   end as revoke_sql
              from (select oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, 'ALL' as privilege, is_grantable
                      from all_privs
                     union all
                    select oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, privilege, is_grantable
                      from compat_tools.dba_detail_privileges x
                     where not exists (select 1 from all_privs where oid = x.oid and grantor = x.grantor and grantee = x.grantee)
                   ) t;

            CREATE OR REPLACE VIEW compat_tools.DBA_ALL_PRIVILEGES AS SELECT oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, privilege, is_grantable FROM compat_tools.DBA_ALL_PRIVILEGES_SQL;

            CREATE OR REPLACE SYNONYM public.DBA_ALL_PRIVILEGES_SQL for compat_tools.DBA_ALL_PRIVILEGES_SQL;
            CREATE OR REPLACE SYNONYM public.DBA_ALL_PRIVILEGES for compat_tools.DBA_ALL_PRIVILEGES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_ALL_PRIVILEGES_SQL: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DUAL
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('SYNONYM', 'DUAL', '1.0')
        then
            select count(*) into l_cnt
              from pg_class
             where relname = 'sys_dummy'
               and relnamespace = (select oid from pg_namespace where nspname = 'pg_catalog')
               and not exists (select 1 from pg_class where relname = 'dual');  -- 忽略已有 dual 对象（避免和 orafce 插件内容冲突）
            if l_cnt = 1
            then
                CREATE OR REPLACE SYNONYM public.DUAL for pg_catalog.sys_dummy;
            end if;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DUAL: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_TABLESPACES
    -- ALL_TABLESPACES
    -- USER_TABLESPACES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TABLESPACES', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_TABLESPACES
            AS
            SELECT case when t.spcname::text = lower(t.spcname::text) then compat_tools.f_upper_name(t.spcname::text) else t.spcname::text end as TABLESPACE_NAME
                 , b.setting as BLOCK_SIZE
                 , t.spcmaxsize as MAX_SIZE
                 , 'ONLINE' as STATUS
                 , 'PERMANENT' as CONTENTS
                 , r.rolname as TABLESPACE_OWNER
              from pg_tablespace as t
              join pg_roles as r on t.spcowner = r.oid
              join (select setting from pg_settings where name = 'block_size') as b on 1=1;

            CREATE OR REPLACE VIEW compat_tools.USER_TABLESPACES AS SELECT tablespace_name, block_size, max_size, status, contents FROM compat_tools.DBA_TABLESPACES WHERE TABLESPACE_OWNER = current_user;

            CREATE OR REPLACE SYNONYM public.DBA_TABLESPACES for compat_tools.DBA_TABLESPACES;
            CREATE OR REPLACE SYNONYM public.ALL_TABLESPACES for compat_tools.DBA_TABLESPACES;
            CREATE OR REPLACE SYNONYM public.USER_TABLESPACES for compat_tools.USER_TABLESPACES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TABLESPACES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$TABLESPACE
    -- GV$TABLESPACE
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$TABLESPACE', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$TABLESPACE
            AS
            SELECT t.oid::bigint as TS#
                 , case when t.spcname::text = lower(t.spcname::text) then compat_tools.f_upper_name(t.spcname::text) else t.spcname::text end as TABLESPACE_NAME
              from pg_tablespace as t;

            CREATE OR REPLACE VIEW compat_tools.GV$TABLESPACE AS SELECT 1 as INST_ID, t.* from compat_tools.V$TABLESPACE as t;

            CREATE OR REPLACE SYNONYM public.V$TABLESPACE for compat_tools.V$TABLESPACE;
            CREATE OR REPLACE SYNONYM public.GV$TABLESPACE for compat_tools.GV$TABLESPACE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$TABLESPACE: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_DATA_FILES
    -- ALL_DATA_FILES
    -- USER_DATA_FILES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_DATA_FILES', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_DATA_FILES
            AS
            SELECT d.setting||'/'||pg_relation_filepath(c.oid) as FILE_NAME
                 , c.oid::bigint as FILE_ID
                 , case when t.spcname is null then compat_tools.f_upper_name('pg_default') when t.spcname::text = lower(t.spcname::text) then compat_tools.f_upper_name(t.spcname::text) else t.spcname::text end as TABLESPACE_NAME
                 , pg_relation_size(c.oid) as BYTES
                 , round(pg_relation_size(c.oid)/b.setting) as BLOCKS
                 , 'AVAILABLE' as STATUS
                 , c.oid::bigint as RELATIVE_FNO
                 , 'YES' as AUTOEXTENSIBLE
                 , t.spcmaxsize as MAXBYTES
                 , round(t.spcmaxsize/b.setting) as MAXBLOCKS
                 , b.setting as INCREMENT_BY
                 , 'ONLINE' as ONLINE_STATUS
                 , n.nspname::text as schema_name
                 , c.relname::text as object_name
                 , case c.relkind
                   when 'r' then 'TABLE'
                   when 'i' then 'INDEX'
                   when 'I' then 'GLOBAL INDEX'
                   when 'S' then 'SEQUENCE'
                   when 'v' then 'VIEW'
                   when 'c' then 'COMPOSITE TYPE'
                   when 't' then 'TOAST'
                   when 'f' then 'FOREIGN TABLE'
                   end as object_type
              from pg_class as c
              join pg_namespace as n on c.relnamespace = n.oid
              left join pg_tablespace as t on c.reltablespace = t.oid
              join (select setting from pg_settings where name = 'block_size') as b on 1=1
              join (select setting from pg_settings where name = 'data_directory') as d on 1=1
             where pg_relation_filepath(c.oid) is not null;

            CREATE OR REPLACE VIEW compat_tools.USER_DATA_FILES AS SELECT FILE_NAME, FILE_ID, tablespace_name, bytes, blocks, status, RELATIVE_FNO, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS, increment_by, ONLINE_STATUS, object_name, object_type FROM compat_tools.DBA_DATA_FILES WHERE schema_name = current_schema();

            CREATE OR REPLACE SYNONYM public.DBA_DATA_FILES for compat_tools.DBA_DATA_FILES;
            CREATE OR REPLACE SYNONYM public.ALL_DATA_FILES for compat_tools.DBA_DATA_FILES;
            CREATE OR REPLACE SYNONYM public.USER_DATA_FILES for compat_tools.USER_DATA_FILES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_DATA_FILES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$DATABASE
    -- GV$DATABASE
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$DATABASE', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.GV$DATABASE
            AS
            SELECT d.oid AS DBID
                 , d.datname::text AS NAME
                 , CASE WHEN t.spcname = 'pg_default' THEN (pg_stat_file('base/' || d.oid || '/PG_VERSION')).modification
                        ELSE (pg_stat_file('pg_tblspc/' || t.oid)).modification
                   END AS CREATED
                 , pg_stat_get_db_stat_reset_time(d.oid) AS RESETLOGS_TIME
                 , case when current_setting('archive_mode') = 'off' then 'NOARCHIVELOG'
                        when current_setting('archive_command') = '(disabled)' then 'MANUAL'
                        else 'ARCHIVELOG'
                   end as LOG_MODE
                 , case when pg_is_in_recovery() then 'STANDBY' else 'CURRENT' end as CONTROLFILE_TYPE
                 , (pg_stat_file('PG_VERSION')).modification AS CONTROLFILE_CREATED
                 , (pg_stat_file('PG_VERSION')).modification AS VERSION_TIME
                 , case when pg_is_in_recovery() then 'READ ONLY' else 'READ WRITE' end as OPEN_MODE -- READ WRITE, READ ONLY, READ ONLY WITH APPLY
                 , 'MAXIMUM PERFORMANCE' AS PROTECTION_MODE
                 , 'UNPROTECTED' AS PROTECTION_LEVEL
                 , 'DISABLE' AS REMOTE_ARCHIVE
                 , case when pg_is_in_recovery() then 'PHYSICAL STANDBY' else 'PRIMARY' end as DATABASE_ROLE
                 , (select case when max(setting) = 'on' then 'ENABLED' else 'DISABLED' end from pg_settings where name = 'enable_wal_shipping_compression') AS ARCHIVELOG_COMPRESSION
                 , 'NOT ALLOWED' AS SWITCHOVER_STATUS
                 , 'NO' AS SUPPLEMENTAL_LOG_DATA_MIN
                 , 'NO' AS SUPPLEMENTAL_LOG_DATA_PK
                 , 'NO' AS SUPPLEMENTAL_LOG_DATA_UI
                 , 'NO' AS FORCE_LOGGING
                 , trim(replace(regexp_substr(version(), 'on\s+([^,])+,'), 'on',''), ', ') AS PLATFORM_NAME
                 , 'NO' AS FLASHBACK_ON
                 , 'NO' AS SUPPLEMENTAL_LOG_DATA_FK
                 , 'NO' AS SUPPLEMENTAL_LOG_DATA_ALL
                 , d.datname AS DB_UNIQUE_NAME
                 , 'DISABLED' AS FS_FAILOVER_STATUS
              FROM pg_database d
              join pg_tablespace t on d.dattablespace = t.oid;

            CREATE OR REPLACE VIEW compat_tools.V$DATABASE AS SELECT * FROM compat_tools.GV$DATABASE where name = current_database()::text;

            CREATE OR REPLACE SYNONYM public.V$DATABASE for compat_tools.V$DATABASE;
            CREATE OR REPLACE SYNONYM public.GV$DATABASE for compat_tools.GV$DATABASE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$DATABASE: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$INSTANCE
    -- GV$INSTANCE
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$INSTANCE', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.GV$INSTANCE
            AS
            with t_ver as (select row_number() over () as id, ver_item from regexp_split_to_table(version(), '\s+') as ver_item)
            SELECT 1 AS INST_ID
                 , 1 AS INSTANCE_NUMBER
                 , current_database() AS INSTANCE_NAME
                 , get_hostname() AS HOST_NAME
                 , ver_item AS VERSION
                 , pg_postmaster_start_time() AS STARTUP_TIME
                 , 'OPEN' AS STATUS
                 , 'NO' AS PARALLEL
                 , 1 AS THREAD#
                 , case when current_setting('archive_mode') = 'off' or current_setting('archive_command') = '(disabled)' then 'STOPPED'
                        else 'STARTED'
                   end as ARCHIVER
                 , 'ALLOWED' AS LOGINS
                 , 'NO' AS SHUTDOWN_PENDING
                 , 'ACTIVE' AS DATABASE_STATUS
                 , 'PRIMARY_INSTANCE' AS INSTANCE_ROLE
                 , 'NORMAL' AS ACTIVE_STATE
                 , 'NO' AS BLOCKED
                 , 'EE' AS EDITION
              from (select ver_item from t_ver where id = (select id - 1 from t_ver where ver_item = 'build')
                     union all
                    select ver_item from t_ver where not exists (select 1 from t_ver where ver_item = 'build') and id = 2);

            CREATE OR REPLACE VIEW compat_tools.V$INSTANCE AS SELECT instance_number, instance_name, host_name, version, startup_time, status, parallel, thread#, archiver, logins, shutdown_pending, database_status, instance_role, active_state, blocked, edition FROM compat_tools.GV$INSTANCE;

            CREATE OR REPLACE SYNONYM public.V$INSTANCE for compat_tools.V$INSTANCE;
            CREATE OR REPLACE SYNONYM public.GV$INSTANCE for compat_tools.GV$INSTANCE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$INSTANCE: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$DATAFILE
    -- GV$DATAFILE
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$DATAFILE', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$DATAFILE
            AS
            SELECT c.oid::bigint as FILE#
                 , o.ctime as CREATION_TIME
                 , c.reltablespace::bigint as TS#
                 , c.oid::bigint as RFILE#
                 , 'ONLINE' as status
                 , 'READ WRITE' as enabled
                 , pg_relation_size(c.oid) as BYTES
                 , round(pg_relation_size(c.oid)/b.setting) as BLOCKS
                 , b.setting as BLOCK_SIZE
                 , d.setting||'/'||pg_relation_filepath(c.oid) as NAME
                 , n.nspname::text as schema_name
                 , c.relname::text as object_name
                 , case c.relkind
                   when 'r' then 'TABLE'
                   when 'i' then 'INDEX'
                   when 'I' then 'GLOBAL INDEX'
                   when 'S' then 'SEQUENCE'
                   when 'v' then 'VIEW'
                   when 'c' then 'COMPOSITE TYPE'
                   when 't' then 'TOAST'
                   when 'f' then 'FOREIGN TABLE'
                   end as object_type
              from pg_class as c
              join pg_namespace as n on c.relnamespace = n.oid
              left join pg_object as o on c.oid = o.object_oid
              join (select setting from pg_settings where name = 'block_size') as b on 1=1
              join (select setting from pg_settings where name = 'data_directory') as d on 1=1
             where pg_relation_filepath(c.oid) is not null;

            CREATE OR REPLACE VIEW compat_tools.GV$DATAFILE AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$DATAFILE t;

            CREATE OR REPLACE SYNONYM public.V$DATAFILE for compat_tools.V$DATAFILE;
            CREATE OR REPLACE SYNONYM public.GV$DATAFILE for compat_tools.GV$DATAFILE;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$DATAFILE: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$LOCK
    -- GV$LOCK
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$LOCK', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$LOCK
            AS
            SELECT distinct l.sessionid as SID
                 , l.pid as PID
                 , l.locktype as TYPE
                 , coalesce(l.relation::regclass::text, l.virtualxid::text) as ID1
                 , coalesce(l.page::regclass::text, l.transactionid::text) as ID2
                 , l.mode as LMODE
                 , extract(epoch from now() - a.xact_start)::bigint as CTIME
              from pg_locks as l
              join pg_stat_activity as a on l.pid = a.pid and l.sessionid = a.sessionid;

            CREATE OR REPLACE VIEW compat_tools.GV$LOCK AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$LOCK t;

            CREATE OR REPLACE SYNONYM public.V$LOCK for compat_tools.V$LOCK;
            CREATE OR REPLACE SYNONYM public.GV$LOCK for compat_tools.GV$LOCK;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$LOCK: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$SYSSTAT
    -- GV$SYSSTAT
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$SYSSTAT', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$SYSSTAT
            AS
            SELECT STAT_ID as STATISTIC#
                 , stat_name as NAME
                 , 1 as CLASS
                 , VALUE
                 , STAT_ID
              FROM GS_INSTANCE_TIME
             UNION ALL
            SELECT 200 as STATISTIC#, 'BGWRITER checkpoints_timed', 2 as CLASS, checkpoints_timed as value, 100 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 201 as STATISTIC#, 'BGWRITER checkpoints_req', 2 as CLASS, checkpoints_req as value, 101 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 202 as STATISTIC#, 'BGWRITER checkpoint_write_time', 2 as CLASS, checkpoint_write_time as value, 102 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 203 as STATISTIC#, 'BGWRITER checkpoint_sync_time', 2 as CLASS, checkpoint_sync_time as value, 103 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 204 as STATISTIC#, 'BGWRITER buffers_checkpoint', 2 as CLASS, buffers_checkpoint as value, 104 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 205 as STATISTIC#, 'BGWRITER buffers_clean', 2 as CLASS, buffers_clean as value, 105 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 206 as STATISTIC#, 'BGWRITER maxwritten_clean', 2 as CLASS, maxwritten_clean as value, 106 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 207 as STATISTIC#, 'BGWRITER buffers_backend', 2 as CLASS, buffers_backend as value, 107 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 208 as STATISTIC#, 'BGWRITER buffers_backend_fsync', 2 as CLASS, buffers_backend_fsync as value, 108 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT 209 as STATISTIC#, 'BGWRITER buffers_alloc', 2 as CLASS, buffers_alloc as value, 109 as STAT_ID FROM PG_STAT_BGWRITER
             UNION ALL
            SELECT STAT_ID, NAME, 3 as CLASS, VALUE, STAT_ID
              FROM (with t as (SELECT sum(numbackends) as numbackends
                                    , sum(xact_commit) as xact_commit
                                    , sum(xact_rollback) as xact_rollback
                                    , sum(blks_read) as blks_read
                                    , sum(blks_hit) as blks_hit
                                    , sum(tup_returned) as tup_returned
                                    , sum(tup_fetched) as tup_fetched
                                    , sum(tup_inserted) as tup_inserted
                                    , sum(tup_updated) as tup_updated
                                    , sum(tup_deleted) as tup_deleted
                                    , sum(conflicts) as conflicts
                                    , sum(temp_files) as temp_files
                                    , sum(temp_bytes) as temp_bytes
                                    , sum(deadlocks) as deadlocks
                                    , sum(blk_read_time) as blk_read_time
                                    , sum(blk_write_time) as blk_write_time
                                 FROM PG_STAT_DATABASE)
                    SELECT 300 as STAT_ID, 'DB numbackends' as NAME, numbackends as VALUE FROM T
                     UNION ALL SELECT 301 as STAT_ID, 'DB xact_commit', xact_commit FROM T
                     UNION ALL SELECT 302 as STAT_ID, 'DB xact_rollback', xact_rollback FROM T
                     UNION ALL SELECT 303 as STAT_ID, 'DB blks_read', blks_read FROM T
                     UNION ALL SELECT 304 as STAT_ID, 'DB blks_hit', blks_hit FROM T
                     UNION ALL SELECT 305 as STAT_ID, 'DB tup_returned', tup_returned FROM T
                     UNION ALL SELECT 306 as STAT_ID, 'DB tup_fetched', tup_fetched FROM T
                     UNION ALL SELECT 307 as STAT_ID, 'DB tup_inserted', tup_inserted FROM T
                     UNION ALL SELECT 308 as STAT_ID, 'DB tup_updated', tup_updated FROM T
                     UNION ALL SELECT 309 as STAT_ID, 'DB tup_deleted', tup_deleted FROM T
                     UNION ALL SELECT 310 as STAT_ID, 'DB conflicts', conflicts FROM T
                     UNION ALL SELECT 311 as STAT_ID, 'DB temp_files', temp_files FROM T
                     UNION ALL SELECT 312 as STAT_ID, 'DB temp_bytes', temp_bytes FROM T
                     UNION ALL SELECT 313 as STAT_ID, 'DB deadlocks', deadlocks FROM T
                     UNION ALL SELECT 314 as STAT_ID, 'DB blk_read_time', blk_read_time FROM T
                     UNION ALL SELECT 315 as STAT_ID, 'DB blk_write_time', blk_write_time FROM T
                   )
             UNION ALL
            SELECT STAT_ID, NAME, 3 as CLASS, VALUE, STAT_ID
              FROM (with t as (SELECT sum(confl_tablespace) as confl_tablespace
                                    , sum(confl_lock) as confl_lock
                                    , sum(confl_snapshot) as confl_snapshot
                                    , sum(confl_bufferpin) as confl_bufferpin
                                    , sum(confl_deadlock) as confl_deadlock
                                 FROM PG_STAT_DATABASE_CONFLICTS)
                    SELECT 316 as STAT_ID, 'DB confl_tablespace' as NAME, confl_tablespace as VALUE FROM T
                     UNION ALL SELECT 317 as STAT_ID, 'DB confl_lock', confl_lock FROM T
                     UNION ALL SELECT 318 as STAT_ID, 'DB confl_snapshot', confl_snapshot FROM T
                     UNION ALL SELECT 319 as STAT_ID, 'DB confl_bufferpin', confl_bufferpin FROM T
                     UNION ALL SELECT 320 as STAT_ID, 'DB confl_deadlock', confl_deadlock FROM T
                   )
             UNION ALL
            SELECT 400 + ID AS STATISTIC#, Name, 4 as CLASS, value, 400 + id STAT_ID
              FROM GS_OS_RUN_INFO;

            CREATE OR REPLACE VIEW compat_tools.GV$SYSSTAT AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$SYSSTAT t;

            CREATE OR REPLACE SYNONYM public.V$SYSSTAT for compat_tools.V$SYSSTAT;
            CREATE OR REPLACE SYNONYM public.GV$SYSSTAT for compat_tools.GV$SYSSTAT;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$SYSSTAT: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$SESSTAT
    -- GV$SESSTAT
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$SESSTAT', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$SESSTAT
            AS
            SELECT sessid as SID, statid AS STATISTIC#, statname as Name, value
              FROM GS_SESSION_STAT
             UNION ALL
            SELECT sessid as SID, stat_id AS STATISTIC#, stat_name as Name, value
              FROM GS_SESSION_TIME;

            CREATE OR REPLACE VIEW compat_tools.GV$SESSTAT AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$SESSTAT t;

            CREATE OR REPLACE SYNONYM public.V$SESSTAT for compat_tools.V$SESSTAT;
            CREATE OR REPLACE SYNONYM public.GV$SESSTAT for compat_tools.GV$SESSTAT;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$SESSTAT: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$NLS_PARAMETERS
    -- GV$NLS_PARAMETERS
    -- NLS_DATABASE_PARAMETERS
    -- NLS_INSTANCE_PARAMETERS
    -- NLS_SESSION_PARAMETERS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$NLS_PARAMETERS', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$NLS_PARAMETERS
            AS
            SELECT NAME AS PARAMETER
                 , SETTING AS VALUE
                 , SHORT_DESC AS DESCRIPTION
              FROM PG_SETTINGS
             WHERE NAME IN ( 'lc_messages'
                           , 'lc_monetary'
                           , 'lc_numeric'
                           , 'lc_time'
                           , 'nls_timestamp_format'
                           , 'lc_collate'
                           , 'lc_ctype')
             UNION ALL
            SELECT 'NLS_CHARACTERSET'
                 , getdatabaseencoding()
                 , 'Database/Server encoding' AS DESCRIPTION;

            CREATE OR REPLACE VIEW compat_tools.GV$NLS_PARAMETERS AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$NLS_PARAMETERS t;
            CREATE OR REPLACE VIEW compat_tools.NLS_DATABASE_PARAMETERS AS SELECT t.* FROM compat_tools.V$NLS_PARAMETERS t;
            CREATE OR REPLACE VIEW compat_tools.NLS_INSTANCE_PARAMETERS AS SELECT t.* FROM compat_tools.V$NLS_PARAMETERS t;
            CREATE OR REPLACE VIEW compat_tools.NLS_SESSION_PARAMETERS
            AS
            SELECT t.* FROM compat_tools.V$NLS_PARAMETERS t where PARAMETER != 'NLS_CHARACTERSET'
             UNION ALL
            SELECT 'NLS_CHARACTERSET'
                 , pg_client_encoding()
                 , 'Client encoding' AS DESCRIPTION;

            CREATE OR REPLACE SYNONYM public.V$NLS_PARAMETERS for compat_tools.V$NLS_PARAMETERS;
            CREATE OR REPLACE SYNONYM public.GV$NLS_PARAMETERS for compat_tools.GV$NLS_PARAMETERS;
            CREATE OR REPLACE SYNONYM public.NLS_DATABASE_PARAMETERS for compat_tools.NLS_DATABASE_PARAMETERS;
            CREATE OR REPLACE SYNONYM public.NLS_INSTANCE_PARAMETERS for compat_tools.NLS_INSTANCE_PARAMETERS;
            CREATE OR REPLACE SYNONYM public.NLS_SESSION_PARAMETERS for compat_tools.NLS_SESSION_PARAMETERS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$NLS_PARAMETERS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- V$NLS_VALID_VALUES
    -- GV$NLS_PARAMETERS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$NLS_VALID_VALUES', '1.0')
        then
            CREATE OR REPLACE VIEW compat_tools.V$NLS_VALID_VALUES
            AS
            SELECT COLLNAME AS NAME
                 , PG_ENCODING_TO_CHAR(COLLENCODING) AS ENCODING
                 , COLLCOLLATE AS COLLATE
                 , COLLCTYPE AS CTYPE
              FROM PG_COLLATION;

            CREATE OR REPLACE VIEW compat_tools.GV$NLS_VALID_VALUES AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$NLS_VALID_VALUES t;

            CREATE OR REPLACE SYNONYM public.V$NLS_VALID_VALUES for compat_tools.V$NLS_VALID_VALUES;
            CREATE OR REPLACE SYNONYM public.GV$NLS_VALID_VALUES for compat_tools.GV$NLS_VALID_VALUES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$NLS_VALID_VALUES: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_JOBS
    -- ALL_JOBS
    -- USER_JOBS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_JOBS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_JOBS
            AS
            SELECT j.job_id as JOB
                 , j.log_user as LOG_USER
                 , j.priv_user as PRIV_USER
                 , j.nspname as SCHEMA_USER
                 , j.start_date
                 , j.last_start_date as LAST_DATE
                 , j.last_suc_date as LAST_SEC
                 , j.last_end_date
                 , j.this_run_date as THIS_DATE
                 , j.next_run_date as NEXT_DATE
                 , extract(second from j.last_end_date - j.last_start_date) as TOTAL_TIME
                 , CASE j.job_status WHEN 'd' then 'Y' else 'N' end as BROKEN
                 , j.interval
                 , j.failure_count as FAILURES
                 , p.what
                 , j.node_name as INSTANCE
                 , case j.job_status when 'r' then 'RUNNING' when 'f' then 'FAILED' when 'd' then 'DISABLE' else 'SUCCESS' end as job_status
                 , j.dbname
              FROM pg_job as j
              JOIN pg_job_proc as p ON j.job_id = p.job_id;

            CREATE OR REPLACE VIEW compat_tools.ALL_JOBS AS SELECT t.* FROM compat_tools.DBA_JOBS t;
            CREATE OR REPLACE VIEW compat_tools.USER_JOBS
            AS
            SELECT JOB
                 , LOG_USER
                 , PRIV_USER
                 , START_DATE
                 , LAST_DATE
                 , LAST_SEC
                 , last_end_date
                 , THIS_DATE
                 , NEXT_DATE
                 , TOTAL_TIME
                 , BROKEN
                 , interval
                 , FAILURES
                 , what
                 , INSTANCE
                 , job_status
                 , dbname
              FROM compat_tools.DBA_JOBS t
             WHERE SCHEMA_USER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_JOBS for compat_tools.DBA_JOBS;
            CREATE OR REPLACE SYNONYM public.ALL_JOBS for compat_tools.ALL_JOBS;
            CREATE OR REPLACE SYNONYM public.USER_JOBS for compat_tools.USER_JOBS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_JOBS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_JOBS_RUNNING
    -- ALL_JOBS_RUNNING
    -- USER_JOBS_RUNNING
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_JOBS_RUNNING', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_JOBS_RUNNING
            AS
            SELECT current_postgres_pid as SID
                 , job_id as JOB
                 , failure_count as FAILURES
                 , last_end_date as LAST_DATE
                 , last_suc_date as LAST_SEC
                 , this_run_date as THIS_DATE
                 , last_suc_date as THIS_SEC
                 , node_name as INSTANCE
              FROM pg_job
             WHERE job_status = 'r';

            CREATE OR REPLACE VIEW compat_tools.ALL_JOBS_RUNNING AS SELECT t.* FROM compat_tools.DBA_JOBS_RUNNING t;

            CREATE OR REPLACE VIEW compat_tools.USER_JOBS_RUNNING
            AS
            SELECT current_postgres_pid as SID
                 , job_id as JOB
                 , failure_count as FAILURES
                 , last_end_date as LAST_DATE
                 , last_suc_date as LAST_SEC
                 , this_run_date as THIS_DATE
                 , last_suc_date as THIS_SEC
                 , node_name as INSTANCE
              FROM pg_job
             WHERE job_status = 'r'
               AND nspname = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_JOBS_RUNNING for compat_tools.DBA_JOBS_RUNNING;
            CREATE OR REPLACE SYNONYM public.ALL_JOBS_RUNNING for compat_tools.ALL_JOBS_RUNNING;
            CREATE OR REPLACE SYNONYM public.USER_JOBS_RUNNING for compat_tools.USER_JOBS_RUNNING;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_JOBS_RUNNING: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_MVIEWS
    -- ALL_MVIEWS
    -- USER_MVIEWS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_MVIEWS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_MVIEWS
            AS
            SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
                 , case when pc.relname::text = lower(pc.relname::text) then compat_tools.f_upper_name(pc.relname::text) else pc.relname::text end AS MVIEW_NAME
                 , case when pc.relname::text = lower(pc.relname::text) then compat_tools.f_upper_name(pc.relname::text) else pc.relname::text end AS CONTAINER_NAME
                 , pg_get_viewdef(mv.matviewid) as QUERY
                 , lengthb(pg_get_viewdef(mv.matviewid)) as QUERY_LEN
                 , 'N' as UPDATABLE
                 , 'DEMAND' as REFRESH_MODE
                 , case when ivm then 'FAST' else 'COMPLETE' end as REFRESH_METHOD
                 , 'IMMEDIATE' as BUILD_MODE
                 , case when ivm then 'DML' else 'NO' end as FAST_REFRESHABLE
                 , case when ivm then 'FAST' else 'COMPLETE' end as LAST_REFRESH_TYPE
                 , refreshtime as LAST_REFRESH_DATE
                 , case when needrefresh then 'STALE' else 'FRESH' end as STALENESS
              FROM gs_matview as mv
              JOIN pg_class as pc on mv.matviewid = pc.oid
              JOIN pg_namespace as nsp on pc.relnamespace = nsp.oid;

            CREATE OR REPLACE VIEW compat_tools.ALL_MVIEWS AS SELECT t.* FROM compat_tools.DBA_MVIEWS t;

            CREATE OR REPLACE VIEW compat_tools.USER_MVIEWS
            AS
            SELECT MVIEW_NAME
                 , CONTAINER_NAME
                 , QUERY
                 , QUERY_LEN
                 , UPDATABLE
                 , REFRESH_MODE
                 , REFRESH_METHOD
                 , BUILD_MODE
                 , FAST_REFRESHABLE
                 , LAST_REFRESH_TYPE
                 , LAST_REFRESH_DATE
                 , STALENESS
              FROM compat_tools.DBA_MVIEWS
             WHERE owner = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_MVIEWS for compat_tools.DBA_MVIEWS;
            CREATE OR REPLACE SYNONYM public.ALL_MVIEWS for compat_tools.ALL_MVIEWS;
            CREATE OR REPLACE SYNONYM public.USER_MVIEWS for compat_tools.USER_MVIEWS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_MVIEWS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_MVIEW_LOGS
    -- ALL_MVIEW_LOGS
    -- USER_MVIEW_LOGS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_MVIEW_LOGS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_MVIEW_LOGS
            AS
            SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS LOG_OWNER
                 , case when rel.relname::text = lower(rel.relname::text) then compat_tools.f_upper_name(rel.relname::text) else rel.relname::text end AS MASTER
                 , case when lc.relname::text = lower(lc.relname::text) then compat_tools.f_upper_name(lc.relname::text) else lc.relname::text end AS LOG_TABLE
              FROM gs_matview_dependency as mvl
              JOIN pg_class as lc on mvl.mlogid = lc.oid
              JOIN pg_class as rel on mvl.relid = rel.oid
              JOIN pg_namespace as nsp on lc.relnamespace = nsp.oid;

            CREATE OR REPLACE VIEW compat_tools.ALL_MVIEW_LOGS AS SELECT t.* FROM compat_tools.DBA_MVIEW_LOGS t;

            CREATE OR REPLACE VIEW compat_tools.USER_MVIEW_LOGS
            AS
            SELECT MASTER
                 , LOG_TABLE
              FROM compat_tools.DBA_MVIEW_LOGS
             WHERE LOG_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_MVIEW_LOGS for compat_tools.DBA_MVIEW_LOGS;
            CREATE OR REPLACE SYNONYM public.ALL_MVIEW_LOGS for compat_tools.ALL_MVIEW_LOGS;
            CREATE OR REPLACE SYNONYM public.USER_MVIEW_LOGS for compat_tools.USER_MVIEW_LOGS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_MVIEW_LOGS: %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- DBA_MVIEW_COMMENTS
    -- ALL_MVIEW_COMMENTS
    -- USER_MVIEW_COMMENTS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_MVIEW_COMMENTS', '2.0')
        then
            CREATE OR REPLACE VIEW compat_tools.DBA_MVIEW_COMMENTS
            AS
            SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
                 , case when rel.relname::text = lower(rel.relname::text) then compat_tools.f_upper_name(rel.relname::text) else rel.relname::text end AS MVIEW_NAME
                 , des.description as COMMENTS
              FROM gs_matview as mv
              JOIN pg_class as rel on mv.matviewid = rel.oid
              JOIN pg_namespace as nsp on rel.relnamespace = nsp.oid
              LEFT JOIN pg_description as des on mv.matviewid = des.objoid;

            CREATE OR REPLACE VIEW compat_tools.ALL_MVIEW_COMMENTS AS SELECT t.* FROM compat_tools.DBA_MVIEW_COMMENTS t;

            CREATE OR REPLACE VIEW compat_tools.USER_MVIEW_COMMENTS
            AS
            SELECT MVIEW_NAME
                 , COMMENTS
              FROM compat_tools.DBA_MVIEW_COMMENTS
             WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);

            CREATE OR REPLACE SYNONYM public.DBA_MVIEW_COMMENTS for compat_tools.DBA_MVIEW_COMMENTS;
            CREATE OR REPLACE SYNONYM public.ALL_MVIEW_COMMENTS for compat_tools.ALL_MVIEW_COMMENTS;
            CREATE OR REPLACE SYNONYM public.USER_MVIEW_COMMENTS for compat_tools.USER_MVIEW_COMMENTS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_MVIEW_COMMENTS: %', SQLERRM;
            rollback;
    end;

    -- =========================================================================
    -- DBA_DIRECTORIES
    -- ALL_DIRECTORIES
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_DIRECTORIES', '1.0')
        then
            create view compat_tools.DBA_DIRECTORIES as
            select u.usename as OWNER ,d.dirname DIRECTORY_NAME,d.dirpath DIRECTORY_PATH
            from pg_catalog.pg_directory d ,pg_catalog.pg_user  u
            where d."owner" =u.usesysid (+);

            CREATE OR REPLACE VIEW compat_tools.ALL_DIRECTORIES AS SELECT t.* FROM compat_tools.DBA_DIRECTORIES t;

            CREATE OR REPLACE SYNONYM public.DBA_DIRECTORIES for compat_tools.DBA_DIRECTORIES;
            CREATE OR REPLACE SYNONYM public.ALL_DIRECTORIES for compat_tools.ALL_DIRECTORIES;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_DIRECTORIES: %', SQLERRM;
            rollback;
    end;

    -- =========================================================================
    -- DBA_ARGUMENTS
    -- ALL_ARGUMENTS
    -- USER_ARGUMENTS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW','DBA_ARGUMENTS','1.0')
        then
        if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.0.9') = 1 then

            create view compat_tools.DBA_ARGUMENTS as 
            SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS owner--
                , case when f.proname::text = lower(f.proname::text) then compat_tools.f_upper_name(f.proname::text) else f.proname::text end AS object_name--
                , CASE WHEN (f.propackageid <> (0)::oid) THEN (case when pkg.pkgname::text = lower(pkg.pkgname::text) then compat_tools.f_upper_name(pkg.pkgname::text) else pkg.pkgname::text end) ELSE NULL::text END AS package_name--
                , a.object_id
                , CASE WHEN ((f.propackage = true) AND ((SELECT count(pg_proc.proname) AS count FROM pg_proc WHERE ((pg_proc.proname = f.proname) AND (pg_proc.propackageid = f.propackageid))) > 1)) THEN (dense_rank() OVER (PARTITION BY f.propackageid, f.proname ORDER BY f.oid))::text ELSE NULL::text END AS overload
                , a.subprogram_id
                , case when a.argument_name::text = lower(a.argument_name::text) then compat_tools.f_upper_name(a.argument_name::text) else a.argument_name::text end AS argument_name
                , a."position"
                , CASE WHEN (f.prokind = 'f'::"char") THEN (a."position" + (1)::numeric) ELSE a."position" END AS sequence
                , (0)::numeric AS data_level
                , CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) WHEN typ.typtype='c' AND ((SELECT COUNT(1) AS count FROM pg_proc WHERE pg_proc.proname = typ.typname ) > 0) THEN 'OBJECT'::text WHEN typ.typtype = 'o' THEN 'TABLE'::text ELSE (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) END AS data_type--
                , CASE WHEN (ARRAY[((a."position" - (1)::numeric))::smallint] <@ (string_to_array((f.prodefaultargpos)::text, ' '))::smallint[]) THEN 'Y'::text ELSE 'N'::text END AS defaulted
                , NULL::text AS default_value
                , NULL::numeric AS default_length
                , CASE WHEN ((a.in_out_abbr)::text = 'i'::text) THEN 'IN'::text WHEN ((a.in_out_abbr)::text = 'o'::text) THEN 'OUT'::text WHEN ((a.in_out_abbr)::text = 'b'::text) THEN 'IN/OUT'::text ELSE 'VARIADIC'::text END AS in_out
                , NULL::numeric as data_length
                , information_schema._pg_numeric_precision(a.data_type_oid, typ.typtypmod)::numeric as data_precision
                , information_schema._pg_numeric_scale(a.data_type_oid, typ.typtypmod)::numeric as data_scale
                , CASE WHEN ((((((((((typ.typname)::text)::text = 'int1'::text) OR (((typ.typname)::text)::text = 'int2'::text)) OR (((typ.typname)::text)::text = 'int4'::text)) OR (((typ.typname)::text)::text = 'int8'::text)) OR (((typ.typname)::text)::text = 'int16'::text)) OR (((typ.typname)::text)::text = 'numeric'::text)) OR (((typ.typname)::text)::text = 'float4'::text)) OR (((typ.typname)::text)::text = 'float8'::text)) THEN (10)::numeric ELSE NULL::numeric END AS radix
                , NULL::text AS character_set_name
                , CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN NULL::text ELSE(case when nsp1.nspname::text = lower(nsp1.nspname::text) then compat_tools.f_upper_name(nsp1.nspname::text) else nsp1.nspname::text end) END AS type_owner--
                , CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN NULL::text ELSE (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) END AS type_name--
                , NULL::text AS type_subname
                , NULL::text AS type_link
                , CASE WHEN (((cla.relkind = 'r'::"char") OR (cla.relkind = 'f'::"char")) OR (cla.relkind = 't'::"char")) THEN 'TABLE'::text WHEN (cla.relkind = 'v'::"char") THEN 'VIEW'::text ELSE NULL::text END AS type_object_type
                , CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) ELSE NULL::text END AS pls_type--
                , NULL::numeric AS char_length
                , CASE WHEN ((((((typ.typname)::text)::text = 'char'::text) OR (((typ.typname)::text)::text = 'bpchar'::text)) OR (((typ.typname)::text)::text = 'varchar'::text)) OR (((typ.typname)::text)::text = 'nvarchar2'::text)) THEN 'B'::text ELSE NULL::text END AS char_used
                , (1)::text AS origin_con_id 
            FROM   (
                    SELECT object_id,subprogram_id,argument_name,"position",
                        CASE WHEN (typ.typbasetype <> 0) THEN typ.typbasetype ELSE a.data_type_oid END AS data_type_oid,
                        in_out_abbr
                    FROM
                    (SELECT f.oid AS object_id
                        , CASE WHEN (f.propackageid <> (0)::oid) THEN (dense_rank() OVER (PARTITION BY f.propackageid ORDER BY f.oid))::numeric ELSE (1)::numeric END AS subprogram_id
                        , (unnest(f.proargnames))::text AS argument_name
                        , CASE WHEN (array_length(f.proargnames, 1) > 1) THEN (generate_series(1, array_length(f.proargnames, 1)))::numeric ELSE (1)::numeric END AS "position"
                        , CASE WHEN (f.proallargtypes IS NOT NULL) THEN unnest(f.proallargtypes) ELSE unnest(f.proargtypes) END AS data_type_oid
                        , CASE WHEN (f.proargmodes IS NOT NULL) THEN (unnest(f.proargmodes))::text ELSE 'i'::text END AS in_out_abbr
                    FROM pg_proc f 
                    UNION ALL
                    SELECT f.oid AS object_id
                        , CASE WHEN (f.propackageid <> (0)::oid) THEN (dense_rank() OVER (PARTITION BY f.propackageid ORDER BY f.oid))::numeric ELSE (1)::numeric END AS subprogram_id
                        , NULL::text AS argument_name
                        , (0)::numeric AS "position"
                        , f.prorettype AS data_type_oid
                        , 'o'::text AS in_out_abbr
                    FROM pg_proc f
                    WHERE f.prokind = 'f'::"char") a
                LEFT JOIN pg_type typ ON typ.oid = a.data_type_oid
                ) a 
            LEFT JOIN pg_proc f ON a.object_id = f.oid
            LEFT JOIN pg_authid n ON f.proowner = n.oid
            LEFT JOIN gs_package pkg ON pkg.oid = f.propackageid
            LEFT JOIN pg_type typ ON typ.oid = a.data_type_oid
            LEFT JOIN pg_namespace nsp1 ON typ.typnamespace = nsp1.oid
            LEFT JOIN pg_class cla ON cla.reltype = typ.oid
            LEFT JOIN pg_namespace nsp ON f.pronamespace = nsp.oid 
            WHERE ((CASE WHEN ((a.in_out_abbr)::text = 'i'::text) THEN 'IN'::text WHEN ((a.in_out_abbr)::text = 'o'::text) THEN 'OUT'::text WHEN ((a.in_out_abbr)::text = 'b'::text) THEN 'IN/OUT'::text ELSE 'VARIADIC'::text END IS NOT NULL)
                AND (pg_has_role(((n.rolname)::text)::name, 'USAGE'::text) OR has_function_privilege(a.object_id, 'EXECUTE'::text)))
            ORDER BY a.object_id, a."position";

        else
            
            create view compat_tools.DBA_ARGUMENTS as 
            SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS owner--
                , case when f.proname::text = lower(f.proname::text) then compat_tools.f_upper_name(f.proname::text) else f.proname::text end AS object_name--
                , NULL::text AS package_name
                , a.object_id
                , CASE WHEN ((f.propackage = true) AND ((SELECT count(pg_proc.proname) AS count FROM pg_proc WHERE ((pg_proc.proname = f.proname))) > 1)) THEN (dense_rank() OVER (PARTITION BY f.proname ORDER BY f.oid))::text ELSE NULL::text END AS overload
                , a.subprogram_id
                , case when a.argument_name::text = lower(a.argument_name::text) then compat_tools.f_upper_name(a.argument_name::text) else a.argument_name::text end AS argument_name
                , a."position"
                , CASE WHEN (f.prokind = 'f'::"char") THEN (a."position" + (1)::numeric) ELSE a."position" END AS sequence
                , (0)::numeric AS data_level
                , CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) WHEN typ.typtype='c' AND ((SELECT COUNT(1) AS count FROM pg_proc WHERE pg_proc.proname = typ.typname ) > 0) THEN 'OBJECT'::text WHEN typ.typtype = 'o' THEN 'TABLE'::text ELSE (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) END AS data_type--
                , CASE WHEN (ARRAY[((a."position" - (1)::numeric))::smallint] <@ (string_to_array((f.prodefaultargpos)::text, ' '))::smallint[]) THEN 'Y'::text ELSE 'N'::text END AS defaulted
                , NULL::text AS default_value
                , NULL::numeric AS default_length
                , CASE WHEN ((a.in_out_abbr)::text = 'i'::text) THEN 'IN'::text WHEN ((a.in_out_abbr)::text = 'o'::text) THEN 'OUT'::text WHEN ((a.in_out_abbr)::text = 'b'::text) THEN 'IN/OUT'::text ELSE 'VARIADIC'::text END AS in_out
                , NULL::numeric as data_length
                , information_schema._pg_numeric_precision(a.data_type_oid, typ.typtypmod)::numeric as data_precision
                , information_schema._pg_numeric_scale(a.data_type_oid, typ.typtypmod)::numeric as data_scale
                , CASE WHEN ((((((((((typ.typname)::text)::text = 'int1'::text) OR (((typ.typname)::text)::text = 'int2'::text)) OR (((typ.typname)::text)::text = 'int4'::text)) OR (((typ.typname)::text)::text = 'int8'::text)) OR (((typ.typname)::text)::text = 'int16'::text)) OR (((typ.typname)::text)::text = 'numeric'::text)) OR (((typ.typname)::text)::text = 'float4'::text)) OR (((typ.typname)::text)::text = 'float8'::text)) THEN (10)::numeric ELSE NULL::numeric END AS radix
                , NULL::text AS character_set_name
                , CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN NULL::text ELSE(case when nsp1.nspname::text = lower(nsp1.nspname::text) then compat_tools.f_upper_name(nsp1.nspname::text) else nsp1.nspname::text end) END AS type_owner--
                , CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN NULL::text ELSE (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) END AS type_name--
                , NULL::text AS type_subname
                , NULL::text AS type_link
                , CASE WHEN (((cla.relkind = 'r'::"char") OR (cla.relkind = 'f'::"char")) OR (cla.relkind = 't'::"char")) THEN 'TABLE'::text WHEN (cla.relkind = 'v'::"char") THEN 'VIEW'::text ELSE NULL::text END AS type_object_type
                , CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) ELSE NULL::text END AS pls_type--
                , NULL::numeric AS char_length
                , CASE WHEN ((((((typ.typname)::text)::text = 'char'::text) OR (((typ.typname)::text)::text = 'bpchar'::text)) OR (((typ.typname)::text)::text = 'varchar'::text)) OR (((typ.typname)::text)::text = 'nvarchar2'::text)) THEN 'B'::text ELSE NULL::text END AS char_used
                , (1)::text AS origin_con_id 
        FROM   (
                SELECT object_id,subprogram_id,argument_name,"position",
                    CASE WHEN (typ.typbasetype <> 0) THEN typ.typbasetype ELSE a.data_type_oid END AS data_type_oid,
                    in_out_abbr
                FROM
                    (SELECT f.oid AS object_id
                        , (1)::numeric AS subprogram_id
                        , (unnest(f.proargnames))::text AS argument_name
                        , CASE WHEN (array_length(f.proargnames, 1) > 1) THEN (generate_series(1, array_length(f.proargnames, 1)))::numeric ELSE (1)::numeric END AS "position"
                        , CASE WHEN (f.proallargtypes IS NOT NULL) THEN unnest(f.proallargtypes) ELSE unnest(f.proargtypes) END AS data_type_oid
                        , CASE WHEN (f.proargmodes IS NOT NULL) THEN (unnest(f.proargmodes))::text ELSE 'i'::text END AS in_out_abbr
                    FROM pg_proc f 
                    UNION ALL
                    SELECT f.oid AS object_id
                        , (1)::numeric AS subprogram_id
                        , NULL::text AS argument_name
                        , (0)::numeric AS "position"
                        , f.prorettype AS data_type_oid
                        , 'o'::text AS in_out_abbr
                    FROM pg_proc f
                    WHERE f.prokind = 'f'::"char") a 
                LEFT JOIN pg_type typ ON typ.oid = a.data_type_oid
                ) a 
            LEFT JOIN pg_proc f ON a.object_id = f.oid
            LEFT JOIN pg_authid n ON f.proowner = n.oid
            LEFT JOIN pg_type typ ON typ.oid = a.data_type_oid
            LEFT JOIN pg_namespace nsp1 ON typ.typnamespace = nsp1.oid
            LEFT JOIN pg_class cla ON cla.reltype = typ.oid
            LEFT JOIN pg_namespace nsp ON f.pronamespace = nsp.oid 
            WHERE ((CASE WHEN ((a.in_out_abbr)::text = 'i'::text) THEN 'IN'::text WHEN ((a.in_out_abbr)::text = 'o'::text) THEN 'OUT'::text WHEN ((a.in_out_abbr)::text = 'b'::text) THEN 'IN/OUT'::text ELSE 'VARIADIC'::text END IS NOT NULL)
                AND (pg_has_role(((n.rolname)::text)::name, 'USAGE'::text) OR has_function_privilege(a.object_id, 'EXECUTE'::text)))
            ORDER BY a.object_id, a."position";

        end if;
            CREATE OR REPLACE VIEW compat_tools.USER_ARGUMENTS AS SELECT object_name,package_name,object_id,overload,subprogram_id,argument_name,position,sequence,data_level,data_type,defaulted,default_value,default_length,in_out,data_length,data_precision,data_scale,radix,character_set_name,type_owner,type_name,type_subname,type_link,type_object_type,pls_type,char_length,char_used,origin_con_id
              FROM compat_tools.DBA_ARGUMENTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
            CREATE OR REPLACE VIEW compat_tools.ALL_ARGUMENTS AS SELECT t.* FROM compat_tools.DBA_ARGUMENTS t;

            CREATE OR REPLACE SYNONYM public.DBA_ARGUMENTS for compat_tools.DBA_ARGUMENTS;
            CREATE OR REPLACE SYNONYM public.ALL_ARGUMENTS for compat_tools.ALL_ARGUMENTS;
            CREATE OR REPLACE SYNONYM public.USER_ARGUMENTS for compat_tools.USER_ARGUMENTS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_ARGUMENTS: %',SQLERRM;
            rollback;
    end;

    -- =========================================================================
    -- V$STATNAME
    -- GV$STATNAME
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$STATNAME', '1.0')
        then
            create view  compat_tools.V$STATNAME as
            SELECT DISTINCT statid STATISTIC# ,
                statname NAME                 ,
                statid STAT_ID                ,
                statname DISPLAY_NAME
            FROM pv_session_stat()
            ORDER BY 1;

            create view compat_tools.GV$STATNAME as select 1 as inst_id, t.* from compat_tools.V$STATNAME as t;

            create synonym public.V$STATNAME for compat_tools.V$STATNAME;
            CREATE OR REPLACE SYNONYM public.GV$STATNAME for compat_tools.GV$STATNAME;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$STATNAME: %', SQLERRM;
            rollback;
    end;

    -- =========================================================================
    -- V$MYSTAT
    -- GV$MYSTAT
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$MYSTAT', '1.0')
        then
            create or replace view compat_tools.V$MYSTAT as
            SELECT pg_backend_pid()::text sid
                ,statid STATISTIC#
                ,value
                ,1::int con_id
            FROM pv_session_stat()
            WHERE regexp_substr(SESSID,'(?<=\.)\d+')=pg_backend_pid()::text;

            create view compat_tools.GV$MYSTAT as select 1 as inst_id, t.* from compat_tools.V$MYSTAT as t;

            create synonym public.V$MYSTAT for compat_tools.V$MYSTAT;
            CREATE OR REPLACE SYNONYM public.GV$MYSTAT for compat_tools.GV$MYSTAT;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$MYSTAT: %', SQLERRM;
            rollback;
    end;
    
    -- =========================================================================
    -- V$LISTENER_NETWORK
    -- GV$LISTENER_NETWORK
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$LISTENER_NETWORK', '1.0')
        then
            create or replace view compat_tools.V$LISTENER_NETWORK as
            select 
            cast(null as varchar2(256)) as network,
            cast ('LOCAL LISTENER' as varchar2(64)) as type,
            cast ('(ADDRESS=(PROTOCOL=TCP)(HOST='||
            case when current_setting('listen_addresses') in ('*','127.0.0.1','0.0.0.0') 
                then inet_out(inet_server_addr())::text 
                else current_setting('listen_addresses') 
            end 
            ||')(PORT='||current_setting('port')||'))' as varchar2(1024))  as value,
            cast(1 as number) as con_id
            union all
            select null,'SERVICE NAME',datname,1 from pg_catalog.pg_database 
            where  case when current_database()= 'postgres' then current_database() else datname end =current_database();

            create view compat_tools.GV$LISTENER_NETWORK as select 1 as inst_id, t.* from compat_tools.V$LISTENER_NETWORK as t;

            CREATE OR REPLACE SYNONYM public.V$LISTENER_NETWORK for compat_tools.V$LISTENER_NETWORK;
            CREATE OR REPLACE SYNONYM public.GV$LISTENER_NETWORK for compat_tools.GV$LISTENER_NETWORK;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in V$LISTENER_NETWORK: %', SQLERRM;
            rollback;
    end;

    -- =========================================================================
    -- DBA_TAB_PRIVS
    -- ALL_TAB_PRIVS
    -- USER_TAB_PRIVS
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_PRIVS', '1.0')
        then
            create view compat_tools.DBA_TAB_PRIVS as 
            select
                case when GRANTEE::text = lower(GRANTEE::text) then compat_tools.f_upper_name(GRANTEE::text) else GRANTEE::text end AS GRANTEE,
                case when schema::text = lower(schema::text) then compat_tools.f_upper_name(schema::text) else schema::text end AS OWNER,
                case when NAME::text = lower(NAME::text) then compat_tools.f_upper_name(NAME::text) else NAME::text end AS TABLE_NAME,
                case when GRANTOR::text = lower(GRANTOR::text) then compat_tools.f_upper_name(GRANTOR::text) else GRANTOR::text end AS GRANTOR,
                privilege,
                case when is_grantable then 'YES' else 'NO' end GRANTABLE,
                'NO'::text hierarchy
            from
                DBA_DETAIL_PRIVILEGES
            where
                column_name is null;

            CREATE OR REPLACE VIEW compat_tools.ALL_TAB_PRIVS AS SELECT t.* FROM compat_tools.DBA_TAB_PRIVS t;
            CREATE OR REPLACE VIEW compat_tools.USER_TAB_PRIVS AS 
            select
                case when GRANTEE::text = lower(GRANTEE::text) then compat_tools.f_upper_name(GRANTEE::text) else GRANTEE::text end AS GRANTEE,
                case when schema::text = lower(schema::text) then compat_tools.f_upper_name(schema::text) else schema::text end AS OWNER,
                case when NAME::text = lower(NAME::text) then compat_tools.f_upper_name(NAME::text) else NAME::text end AS TABLE_NAME,
                case when GRANTOR::text = lower(GRANTOR::text) then compat_tools.f_upper_name(GRANTOR::text) else GRANTOR::text end AS GRANTOR,
                privilege,
                case when is_grantable then 'YES' else 'NO' end GRANTABLE,
                'NO'::text hierarchy
            from
                DBA_DETAIL_PRIVILEGES
            where
                column_name is null
            AND current_schema() IN (GRANTEE,schema,GRANTOR);

            CREATE OR REPLACE SYNONYM public.DBA_TAB_PRIVS for compat_tools.DBA_TAB_PRIVS;
            CREATE OR REPLACE SYNONYM public.ALL_TAB_PRIVS for compat_tools.ALL_TAB_PRIVS;
            CREATE OR REPLACE SYNONYM public.USER_TAB_PRIVS for compat_tools.USER_TAB_PRIVS;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_PRIVS: %', SQLERRM;
            rollback;
    end;

    -- =========================================================================
    -- V$LOCKED_OBJECT
    -- =========================================================================
    begin
        if compat_tools.drop_compat_object('VIEW', 'V$LOCKED_OBJECT', '1.0')
        then
            create view compat_tools.v$locked_object as 
            SELECT 
                null::int4 AS xidusn,
                null::int4 AS xidslot,
                null::int4 AS xidsqn,
                case locktype 
                    when 'partition' then objid::int4
                    else nvl(relation,classid)::int4 
                end AS object_id,
                l.sessionid AS session_id,
                t.usename AS oracle_username,
                null::text  AS os_user_name,
                l.pid  AS process,
                /*
                0 - none
                1 - null (NULL)
                2 - row-S (SS)
                3 - row-X (SX)
                4 - share (S)
                5 - S/Row-X (SSX)
                6 - exclusive (X)
                */
                case  granted when true THEN
                    (CASE l.mode
                        WHEN 'AccessShareLock'::text THEN 2
                        WHEN 'RowShareLock'::text THEN 2
                        WHEN 'RowExclusiveLock'::text THEN 3
                        WHEN 'ShareLock'::text THEN 4
                        when 'ShareUpdateExclusiveLock' then 5
                        when 'ShareRowExclusiveLock' then 5
                        WHEN 'ExclusiveLock'::text THEN 6
                        WHEN 'AccessExclusiveLock'::text THEN 6
                        ELSE 0
                    end)::int4 else 1::INT4 END AS locked_mode
            FROM pg_locks l,
                pg_stat_activity t
            WHERE l.pid = t.pid(+) 
            and l.sessionid=t.sessionid(+)
            and l.locktype in ('object','relation','page','tuple','partition');

            CREATE OR REPLACE VIEW compat_tools.GV$LOCKED_OBJECT AS SELECT 1 as inst_id, t.* FROM compat_tools.V$LOCKED_OBJECT as t;

            CREATE OR REPLACE SYNONYM public.V$LOCKED_OBJECT for compat_tools.V$LOCKED_OBJECT;
            CREATE OR REPLACE SYNONYM public.GV$LOCKED_OBJECT for compat_tools.GV$LOCKED_OBJECT;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in DBA_TAB_PRIVS: %', SQLERRM;
            rollback;
    end;
end;
$VIEW_CREATION$ language plpgsql;


-- =============================================================================
-- 根据 public synonym 创建 sys synonym
-- =============================================================================
do $SYS_SYNONYM$
declare
    l_sym_record    record;
    l_cnt           bigint;
begin
    if current_setting('application_name') != 'checkMe'
    then
        -- 创建 sys schema
        select count(*) into l_cnt
          from pg_catalog.pg_namespace
         where nspname = 'sys';
        if l_cnt = 0
        then
            create schema sys;
        end if;

        -- 创建 sys synonym
        for l_sym_record in select synname, synobjname
                              from pg_catalog.pg_synonym
                             where synobjschema = 'compat_tools'
                               and synnamespace = (select oid
                                                     from pg_catalog.pg_namespace
                                                    where nspname = 'public')
                               and not exists (select synname
                                                 from pg_catalog.pg_synonym
                                                where synnamespace = (select oid
                                                                        from pg_catalog.pg_namespace
                                                                       where nspname = 'sys'))
        loop
            -- raise notice '%', 'create synonym sys.'||l_sym_record.synname||' for compat_tools.'||l_sym_record.synobjname;
            execute 'create synonym sys.'||l_sym_record.synname||' for compat_tools.'||l_sym_record.synobjname;
        end loop;

        -- 创建 sys.dual synonym
        for l_sym_record in select 1
                              from pg_catalog.pg_class
                             where relname = 'sys_dummy'
                               and relnamespace = (select oid
                                                     from pg_catalog.pg_namespace
                                                    where nspname = 'pg_catalog')
                               and not exists (select 1
                                                 from pg_catalog.pg_synonym
                                                where synname = 'dual'
                                                  and synnamespace = (select oid
                                                                        from pg_catalog.pg_namespace
                                                                       where nspname = 'sys'))
        loop
            -- raise notice '%', 'create synonym sys.dual for pg_catalog.sys_dummy';
            create synonym sys.dual for pg_catalog.sys_dummy;
        end loop;
    end if;
exception
    when others then
        RAISE WARNING 'Error in sys synonym creation: %', SQLERRM;
        rollback;
end;
$SYS_SYNONYM$ language plpgsql;


-- Show result & Exit
do $RESULT_SUMMARY$
declare
    l_app_name  text  := current_setting('application_name');
begin
    set client_min_messages='notice';
    if l_app_name not in ('runMe', 'checkMe')
    then
        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Compat Object List: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   |' || pad_char
                              || rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
                            from (select greatest(max(length(object_type)), 5) max_object_type
                                       , greatest(max(length(object_name)), 6) max_object_name
                                       , greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
                                       , greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
                                       , greatest(max(length(operation)), 9) max_operation
                                    from temp_result) l
                            join (select 'type' as object_type
                                       , 'name' as object_name
                                       , 'version' as object_version
                                       , 'language' as object_language
                                       , 'operation' as operation
                                       , ' ' as pad_char
                                   union all
                                  select '-' as object_type
                                       , '-' as object_name
                                       , '-' as object_version
                                       , '-' as object_language
                                       , '-' as operation
                                       , '-' as pad_char
                                   union all
                                  select object_type, object_name
                                       , case when local_version = script_version then local_version else local_version || ' => ' || script_version end as object_version
                                       , case when local_language = script_language then local_language else local_language || ' => ' || script_language end as object_language
                                       , operation, ' ' from temp_result) r on 1 = 1
        loop
            raise notice '%', l_app_name;
        end loop;
    end if;
end;
$RESULT_SUMMARY$ language plpgsql;

reset behavior_compat_options;

\q
